7 Replies Latest reply: Aug 14, 2013 10:51 AM by 1018245 RSS

    Calculations and Logics in Data Form Requirement

    1018245

      Hello Guru!

       

      I have a requirement to calculate salaries per position per department. We get yearly salary and then I need to distribute/spread that salary over 12 months per business days. See below for example:

       

      JanFebMarAprMayJunJulAugSepOctNovDecTotal
      Begin day 1/1/20132/1/20133/1/20134/1/20135/1/20136/1/20137/1/20138/1/20139/1/201310/1/201311/1/201312/1/20131/1/2013
      End day 1/31/20132/28/20133/31/20134/30/20135/31/20136/30/20137/31/20138/31/20139/30/201310/31/201311/30/201312/31/201312/31/2013
      Workdays 232021222320232221232122261
      Month allocation 8.81%7.66%8.05%8.43%8.81%7.66%8.81%8.43%8.05%8.81%8.05%8.43%100.00%
      Input
      $                        55,000          4,847          4,215          4,425          4,636          4,847          4,215          4,847          4,636          4,425            4,847            4,425            4,636          55,000

       

      Is this possible using data forms and calc scripts...if so, can you please give me a broad idea on which functions to use and I will do the rest thank you in advance.

        • 1. Re: Calculations and Logics in Data Form Requirement
          1018245

          It is a classic planning application 11.1.2.1.600, we do not use EPMA.

          • 2. Re: Calculations and Logics in Data Form Requirement
            _RahulS_

            Assuming you are inputting Salary at BegBalance

            FIX(....)

            IF(@ISIDESC("YearTotal"))

            "Salary"(

            "BegBalance"->"Salary" * "Month Allocation";

            )

            ENDFIX

            • 3. Re: Calculations and Logics in Data Form Requirement
              1018245

              Thank you. I can simply allow calc scripts calculation instead of using a data form.

               

              Question: I will only have year total salary. May I please request you to explain me the logic of having salary at BegBalance? Also, Month allocation is basically a numbered formula that varies month to month. So in this case, I see you have placed "Month Allocation" as member that I do not have at this point in the outline. Can I just place the number there? and start if/else statement saying if month = jan...salary* the numbered formula?

              • 4. Re: Calculations and Logics in Data Form Requirement
                _RahulS_

                You can create data form to enter Salary at BegBalance->Salary combination. To explain your query:

                 

                FIX(....)

                IF(@ISIDESC("YearTotal"))

                "Salary"(

                "BegBalance"->"Salary" * "Month Allocation";

                )

                ENDFIX

                 

                 

                Above snippet will do the following:

                 

                Jan->Salary =  BegBalance->Salary * Jan->Month Allocation

                Feb->Salary =  BegBalance->Salary * Feb->Month Allocation

                .

                .

                .

                Dec->Salary =  BegBalance->Salary * Dec->Month Allocation



                If you dont have the member either you can create it or yes you can have the direct number as well.

                Ex:

                FIX(....)

                Salary

                (

                Jan = "BegBalance"->"Salary" * X1

                Feb = "BegBalance"->"Salary" * X2

                .

                .

                .

                Dec= "BegBalance"->"Salary" * X2

                )

                ENDFIX




                If you have Salary at YearTotal then:

                 

                FIX(....)

                IF(@ISIDESC("YearTotal"))

                "Salary"(

                "YearTotal"->"Salary" * "Month Allocation";

                )

                ENDFIX

                 

                 

                Cheers..

                Rahul S.

                • 5. Re: Calculations and Logics in Data Form Requirement
                  1018245

                  Thank you Rahul,

                   

                  you typed script much for me..i m very grateful.

                   

                  My question on this:

                   

                  If you have Salary at YearTotal then:

                   

                  FIX(....)

                  IF(@ISIDESC("YearTotal"))

                  "Salary"(

                  "YearTotal"->"Salary" * "Month Allocation";

                  )

                  ENDFIX

                   

                  How will each differnt month get its own allocation in the form. i have 13 columns..as soon as the user puts in amount in year total column, the months columns should calculate their own share depending on the above allocation. but how will one script allocate salary amount in all different column...do i add another if statement saying if month is Jan...do this allocation? also, do I need to place ENDIF?

                  • 6. Re: Calculations and Logics in Data Form Requirement
                    _RahulS_

                    If you will mention any any member from Period Dim with "Mothly Allocation" it will perform the calculation for every member.

                    Try to execute it, you will understand. If you many calc to improve or trying to introduce a new process its worth asking for a consultant for a short duration.

                     

                     

                    Cheers..

                    Rahul S.

                    • 7. Re: Calculations and Logics in Data Form Requirement
                      1018245

                      Thank you Rahul,

                       

                      One quick hurdle. I want to create a data form using teh same logic as above. However, when I create it, the data cells are read only/greyed out.

                       

                      I need users be able to input year total amount on the basis of which monthly allocations are calculated.

                       

                      I know the level 0 members are not greyed out. However, JAn - Dec are greyed out data cells

                       

                      My approach is to create a new level 0 member in Year dimension (as begBalance is biengs used) to hold the salary in data form for the month of  Jan. and then it calculates amounts for rest of months and the Year total aggregates accordingly. Please advise on the greyed out data cells.