SAP Analytics Cloud Planning Data Action Advance Formula

[pragi2028]

Hi all ,

I have a requirement where a certain percentage of amount for certain cost center should detected and get stored to a GL called Direct. For example refer below amount 2000 from Cost center 101 where 10% ie. 200 must get allocated to Direct. How can I perform this using Advanced formula in Data Action

Thanks!! in advance

SAP Analytics Cloud SAP Analytics Cloud for planning 

[N1kh1l]

@pragi2028 

The solution will depend on whether the percentage of DIRECT deduction is based on Cost center or Costcenter and GL combination. If it is just based on costcenter you can maintain these percentages in property ( type number) of costcenter and use it in DA. If its based on Costcenter and GL then you need to maintain these percentages in system at CC and GL combination and may be other dimension as #.

First approach:

MEMBERSET [D/Account]=("A","B","C","D","E")

DATA([d/Account]="Direct") = ATTRIBUTE([d/costcenter].[p/directperc])*RESULTLOOKUP()

 Second Approach

MEMBERSET [D/Account]=("A","B","C","D","E")
DATA([d/Account]="Direct") = RESULTLOOKUP([Account]="DirectPerc") * RESULTLOOKUP()

In RESULTLOOKUP([Account]="DirectPerc") please specify all other dimension as # if you use them to store the percentages.

Hope this helps !!

Nikhil

[akhilgs]

Hi, you can try something like below

DATA([d/Measures] = "amount", [d/Cost Center] = "101", [d/GL] = "DIRECT") = (RESULTLOOKUP([d/Measures] = "Percentage", [d/Cost Center] = "101", [d/GL] = "A" )/100)*RESULTLOOKUP([d/Measures] = "amount", [d/Cost Center] = "101", [d/GL] = "A" ) 

This would take the measure amount as "2000" in the combination cost center "101' & GL "A" and multiple it by measure Percentage as "10" in the combination cost center "101" & GL "A" and store the value in the measure amount in the combination cost center "101" & GL "DIRECT".

[pragi2028]

@akhilgs

I have my percentage stored only in jan 2024 but I have to multiply it with the total (full 2024) of 500200 gl Monthly salary how can i achieve this with the advanced formula. Thanks

[akhilgs]

Hi @pragi2028 , for that you just need to further reduce the scope of the calculation to take only the date Jan 2024 in the case of percentage, your advanced formula  should look like below, you need to set the measures and dimensions names to what you need, since it is needed for the whole year, you would need to loop the calculation for the whole date set, so in the below case, the value for date would remain constant for "Percentage" while for other measure, the date would be the whole of 2024.

MEMBERSET [d/Date] = "202401" TO "202412"
FOREACH [d/Date]
	DATA([d/Measures] = "amount", [d/Cost Center] = "101", [d/GL] = "DIRECT") = (RESULTLOOKUP([d/Measures] = "Percentage", [d/Cost Center] = "101", [d/GL] = "A" , [d/Date] = "202401")/100)*RESULTLOOKUP([d/Measures] = "amount", [d/Cost Center] = "101", [d/GL] = "A" )
ENDFO