Formula to sum up values of GL accounts then multipy them by percentages

[former_member246680]

Dear gurus,

I have a requirement to sum up the values of certain GL accounts which have a certain attribute. When I get the summation, I am then supposed to multiply the total value by some periodic percentage values which are sourced from a different datasource from which this calculation is being done.

I have written the following formula and need your help to make it work.

Fields to be changed: {keyfigurename,0comp_code,0fiscper,0infoprov,0glaccount)

DATA KF TYPE KEYFIGURE_NAME.

DATA SUM TYPE F.

DATA VAL TYPE F.

DATA CC TYPE 0COMP_CODE.

DATA FP TYPE 0FISCPER.

DATA GL TYPE 0GLACCOUNT.

DATA CUBE TYPE 0INFOPROV.

GL = ATRV(0INCST_FLAG, GL).

FOR EACH GL.

SUM = 0.

SUM = SUM + {0SALES, CC, FP, CUBE, GL}.

ENDFOR.

This part is just for the summation in the current cube where the calculation is taking place. When I execute the planning sequence, it just reads values but it is not generating any record. Please help first on this summation part.

Then for the second part where I have to multiply the total value with some percentage values which are coming from another datasource, the percentage values are stored against 0COMP_CODE and a keyfigure  Ynumber.

How do I continue with my code to do this multiplication so as to get the total value? I have created a multiprovider which contains both the calculation cub and the DSO which contains the percentages.

Your assistance will be greatly appreciated.

Regards

Jordan

[cornelia_lezoch · Accepted solution]

Hi jordan,

first this line is not correct

GL = ATRV(0INCST_FLAG, GL).

GL can not be an account and it´s attribute.

define a different name for the attribute.

What is the value for this attribute that you want to check?

regards

Cornelia

[former_member246680 · Accepted solution]

Hi Cornilea,

The attribute value is the 0INCST_FLAG which picks only GL accounts flagged as income statements GLaccounts. I thought that was the way to write it.

Regards

Jordan

[cornelia_lezoch · Accepted solution]

Hello Jordan,

still you do not name the value. Is it an X?

assuming it´s an X for the records that you want to sum the coding for that part would be:

data flag type 0INCST_FLAG.

data GL type 0glaccount.

data sum type f.

sum = 0.

foreach GL.

flag = ATRV(0INCST_FLAG, GL).

if flag = X.

sum = sum + ...

endif.

endfor.

regards

Cornelia

[former_member246680 · Accepted solution]

Thanks a lot Cornelia,

The attribute value is X. I have written the code exactly as you say, but it is only able to read the number of records, 0 generated, 0 changed, 0 deleted.

the code has a lot of fields to be changed because for the second part where I want to read data from a dso and multiply it by the sum in the first part, I intend to write a function module to read that data. But I cant get to that part until I manage to generate data on the first summation part

Regards

Jordan

[cornelia_lezoch · Accepted solution]

then please show the current status of your code

[former_member246680 · Accepted solution]

Hi Cornelia, The code is as follows: DATA KF TYPE KEYFIGURE_NAME. DATA CC TYPE 0COMP_CODE. DATA CUR TYPE 0CURRENCY. DATA FP TYPE 0FISCPER. DATA PP TYPE 0FISCPER3. DATA VAR TYPE 0FISCVARNT. DATA YEAR TYPE 0FISCYEAR. DATA JN TYPE ZBPCJNLID. DATA PC TYPE ZMIPROFIT. DATA GL TYPE ZMIGLACC. DATA VER TYPE ZMIVER. DATA VT TYPE ZMIVTYP. DATA CURR TYPE 0CURTYPE. DATA FLAG TYPE 0INCST_FLAG. DATA PL TYPE F. DATA MSH TYPE F. DATA VALUE TYPE F. VAR = 'K4'. VER = VARV( ZFM_VER ). YEAR = '2015'. PL = 0. FOREACH GL. FLAG = ATRV(0INCST_FLAG, GL). IF FLAG = X. PL = PL + {0SALES, CC, CUR, CURR, FP, PP, VAR, YEAR, JN, GL, PC, VER, VT}. ENDIF. FOREACH YEAR, PP, PC, CUR. CALL FUNCTION Y_BPC_GET_PARAM   EXPORTING       I_PC = PC     I_FV = VAR       I_FY = YEAR     I_PP = PP     I_CURR = CUR       I_VER = VR       I_CT = CURR     I_VT = VT     I_JNR = JN     IMPORTING     E_RT_F = MSH. FOREACH YEAR, PP, PC. VALUE = MSH*PL. ENDFOR. ENDFOR. ENDFOR. the first foreach is for calculating the summation of GL accounts marked as PL, the second foreach is a function to get percentage rates from a DSO, the third foreach is to calculate the summation(profit or loss) by the rates from the dso. For now I can only read some records in the first part bt no data changed or generated. Regards Jordan

[former_member246680 · Accepted solution]

Sorry let me put it nicely

[former_member246680 · Accepted solution]

hi Cornelia

the code is now as follows:

DATA KF TYPE KEYFIGURE_NAME.

DATA CC TYPE 0COMP_CODE.

DATA CUR TYPE 0CURRENCY.

DATA FP TYPE 0FISCPER.

DATA PP TYPE 0FISCPER3.

DATA VAR TYPE 0FISCVARNT.

DATA YEAR TYPE 0FISCYEAR.

DATA JN TYPE ZBPCJNLID.

DATA PC TYPE ZMIPROFIT.

DATA GL TYPE ZMIGLACC.

DATA VER TYPE ZMIVER.

DATA VT TYPE ZMIVTYP.

DATA CURR TYPE 0CURTYPE.

DATA FLAG TYPE 0INCST_FLAG.

DATA PL TYPE F.

DATA MSH TYPE F.

DATA VALUE TYPE F.

VAR = 'K4'.

VER = VARV( ZFM_VER ).

YEAR = '2015'.

PL = 0.

FOREACH GL.

FLAG = ATRV(0INCST_FLAG, GL).

IF FLAG = X.

PL = PL + {0SALES, CC, CUR, CURR, FP, PP, VAR, YEAR, JN, GL, PC, VER, VT}.

ENDIF.

FOREACH YEAR, PP, PC, CUR.

CALL FUNCTION Y_BPC_GET_PARAM 

EXPORTING  

--------

------  

     IMPORTING   

-----

.

FOREACH YEAR, PP, PC.

VALUE = MSH*PL.

ENDFOR.

.

ENDFOR.

ENDFOR.

the 1st foreach is for summation of gl values marked as PL. the 2nd foreach is a function to retrieve percentages from a dso. the 3rd foreach is for multiplying these percenatges with the summation result above(profit or loss)

right now im just able to read values in the first forech but not able to generate anything

Regards

Jordan

[cornelia_lezoch · Accepted solution]

Hello Jordan,

your code is very confusing.

I doubt that you need so many fields to be changed, because I can not see where the fields are changing. Why have you defined them as to be changed?

also it is not wise to have several foreach loops within each other - probably this can be solved by defining less fields to be changed.

when creating a formula you should do it step by step and not define 3 loops in the first place, bcause it is hard to identify errors.

so in the first step, ry to make the summation run. I test thse things using the message funktionality and let the system show me via message if the summation creates the right result.

in your case, if there are really all those fields to be changed necessary, you probably need a code like

FOREACH GL.

FLAG = ATRV(0INCST_FLAG, GL).

IF FLAG = X.

foreach all other fields (put your list here)

if {0SALES, CC, CUR, CURR, FP, PP, VAR, YEAR, JN, GL, PC, VER, VT} <>0

PL = PL + {0SALES, CC, CUR, CURR, FP, PP, VAR, YEAR, JN, GL, PC, VER, VT}.

endif.

endfor.

ENDIF.

endfor.

If you really need those other foreaches, you need to cut the lists of fields into those pieces like

1. loop - Field A, B C

2. loop - field D, E, F

3. loop - field G, H, I

then we can not see where the data  for MSH is coming from, so probably the system is not able to fill this with data and will calculate using a 0.

then this code

FOREACH YEAR, PP, PC.

VALUE = MSH*PL.

ENDFOR.

is nonsense. If you do not define an operand using YEAR, PP, PC the system can not do any loop.

And by the way - if you do get answers here in SCN which are helpful - then mark them as helpful. And mark you thread in the end as answered.

regards

Cornelia

[former_member246680 · Accepted solution]

Hi Cornelia,

Thanks for your assistance. Minimising foreach statements is very efficient as you recommended. The fields to be changed are many because there is also data to be pulled from another data source.

Regards Jordan

[former_member246680]

Hi all,

anyone please help with this coding.

Regards

Jordan