12 Replies Latest reply: Mar 23, 2012 2:19 AM by Ramya A

# Formula Planning Function

Currently Being Moderated

I have a scenario where I have to use two rows to calculate a formula.

Ex:

EMP     Class       GL           Sal        Proj

5           510000    510001    1000      100

5           520000    520001     5000      x

x= 5000/1000 * 100

Value x should be populated by using the above formula. Employee is common for both rows. Using FOREACH EMP is not working.

• ###### Re: Formula Planning Function
Currently Being Moderated

Hi

You need to write nested loops for picking up the correct record as given in example below:-

I m not sure which new record you will generating with X KF value ,so keep those characteristics in fields to be changed and remaining read as reference data.

For each EMP.

For each Class, GL, emp1 in refdata.

If emp = emp1.

var_sal1 = { Sal,emp,class,Gl}.

var_proj  = { Proj,emp,class,Gl}.

If var_proj = 0.

var_sal2 = var_sal1.

var_sal1 = 0.

end if.

endif.

endfor.

var_x = (var_sal1 / var_sal2) * var_proj.

end for.

It just based on my understanding you can modify as per your requirement.

• ###### Re: Formula Planning Function
Currently Being Moderated

Thanks Indu.

Quick question.

If var_proj = 0.

var_sal2 = var_sal1.

var_sal1 = 0.

I have to define var_sal2 as well as var_sal1 right? Since it has a different GL associated with it?

• ###### Re: Formula Planning Function
Currently Being Moderated

Yes,it has  two different GL associated with it but how will you identify which sal will be denominator and numerator ,So i thought we can check that on projection(proj).

yes you have to declare both.

• ###### Re: Formula Planning Function
Currently Being Moderated

I have used the below formula:

DATA EMP TYPE 0EMPLOYEE.

DATA GL TYPE 0GL_ACCOUNT.

DATA SPCL TYPE 0GM_SPNCL.

DATA SPCL61 TYPE 0GM_SPNCL.

DATA SPCL62 TYPE 0GM_SPNCL.

SPCL61 = 610000.

SPCL62 = 620000.

FOREACH EMP.

FOREACH SPCL,GL.

{ 0AMOUNT,EMP,0000620001,SPCL62,ZPI_T01 } = ({ 0OI_WRBTR,EMP,GL,SPCL62,ZPI_C08}/{

0OI_WRBTR,EMP,GL,SPCL61,ZPI_C08})*{ 0AMOUNT,EMP,GL,SPCL61,ZPI_T01 }.

ENDFOR.

ENDFOR.

What am I doing wrong in GL part?  Whereever GL is used,Instead of making it a constant how can I make it to look at the corresponding GL from the row?

• ###### Re: Formula Planning Function
Currently Being Moderated

How do I define these variables var1,var2... ? Thanks!

• ###### Re: Formula Planning Function
Currently Being Moderated

You need to be clear about your requirement.If GL and SPCL are constants across records you can hard code it in fox formula but if it is changing with records and has got multiple values you have to use variable in foreach .

Secondly you can declare variable as mentioned below:-

Data var_sal type F.(F for floating type)

Data Var_sal type I .(I for integer)

One more thing you are using two different Infoprovider ,restrict one infoprovider in aggregation level to which you want to write (ZPI_T01) and read other as reference data.

Try out code given below,i don't have clear visibility what records you want to generate .you can modify as per requirement.

DATA EMP TYPE 0EMPLOYEE.

DATA EMP1 TYPE 0EMPLOYEE.

DATA GL TYPE 0GL_ACCOUNT.

DATA SPCL TYPE 0GM_SPNCL.

Data var_62 type F.

Data var_61 type F.

FOREACH EMP.

FOREACH SPCL,GL,emp1 in refdata.

If emp = emp1 and SPCL = 620000 .

var_62 = {0OI_WRBTR,EMP,GL,SPCL,ZPI_C08}.

else if emp = emp1 and SPCL = 610000 .

var_61 = {0OI_WRBTR,EMP,GL,SPCL61,ZPI_C08}.

endif.

endfor.

{0AMOUNT,EMP,0000620001,620000,ZPI_T01 } = (var_62/var_61) *{0AMOUNT,EMP,0000610001,610000,ZPI_T01 }.

ENDFOR.

• ###### Re: Formula Planning Function
Currently Being Moderated

I dont want to use constant. It should automatically read the lines with corresponding Class and GL for each employee and do the calculation. Is that possible?

• ###### Re: Formula Planning Function
Currently Being Moderated

yes,its possible using variables.

DATA EMP TYPE 0EMPLOYEE.

DATA EMP1 TYPE 0EMPLOYEE.

DATA GL TYPE 0GL_ACCOUNT.

DATA SPCL TYPE 0GM_SPNCL.

DATA GL1 TYPE 0GL_ACCOUNT.

DATA SPCL1 TYPE 0GM_SPNCL.

Data var_62 type F.

Data var_61 type F.

data var_proj type F.

FOREACH EMP, SPCL1,GL1.

var_proj ={0AMOUNT,EMP,SPCL1,GL1,ZPI_T01 }.

FOREACH SPCL,GL,emp1 in refdata.

If emp = emp1 and SPCL <> SPCL1.

var_62 = {0OI_WRBTR,EMP,GL,SPCL,ZPI_C08}.

else if emp = emp1 and SPCL == SPCL1 and var_proj <> 0 .

var_61 = {0OI_WRBTR,EMP,GL,SPCL,ZPI_C08}.

endif.

endfor.

{0AMOUNT,EMP,GL1,SPCL1,ZPI_T01 } = (var_62/var_61) *var_proj.

ENDFOR.

• ###### Re: Formula Planning Function
Currently Being Moderated

Thank you so much! One more question:

FOREACH EMP, SPCL1,GL1.

VAR_PROJ = {0AMOUNT,EMP,GL1,SPCL1,ZPI_T01 }.

FOREACH SPCL,GL,EMP1 IN REFDATA.

IF EMP = EMP1 AND SPCL <> SPCL1.

VAR_62 = {0OI_WRBTR,EMP,GL,SPCL,ZPI_C08}.

ELSEIF EMP = EMP1 AND SPCL = SPCL1 AND VAR_PROJ <> 0 .

VAR_61 = {0OI_WRBTR,EMP,GL,SPCL,ZPI_C08}.

ENDIF.

ENDFOR.

{0AMOUNT,EMP,GL1,SPCL1,ZPI_T01 } = (VAR_62/VAR_61) *VAR_PROJ.

ENDFOR.

VAR_PROJ(61) value is getting updated instead of the  VAR_Proj(62).

How do I define these two independently and then pass the value?

• ###### Re: Formula Planning Function
Currently Being Moderated

EMP     Class       GL           Sal        Proj

5           510000    510001    1000      100

5           520000    520001     5000      x

Instead of x, the row which has Proj = 100 is getting updated. Any suggestions? Thanks!

• ###### Re: Formula Planning Function
Currently Being Moderated

Actually both the rows are getting updated. Any help is appreciated.

• ###### Re: Formula Planning Function
Currently Being Moderated

Can you tell me clearly how should be your resulted record should look like.