7 Replies Latest reply on Aug 14, 2013 3:51 PM by 1018245

# Calculations and Logics in Data Form Requirement

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:

 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Begin day 1/1/2013 2/1/2013 3/1/2013 4/1/2013 5/1/2013 6/1/2013 7/1/2013 8/1/2013 9/1/2013 10/1/2013 11/1/2013 12/1/2013 1/1/2013 End day 1/31/2013 2/28/2013 3/31/2013 4/30/2013 5/31/2013 6/30/2013 7/31/2013 8/31/2013 9/30/2013 10/31/2013 11/30/2013 12/31/2013 12/31/2013 Workdays 23 20 21 22 23 20 23 22 21 23 21 22 261 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

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

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

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

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

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

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

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.