cancel
Showing results for 
Search instead for 
Did you mean: 

SAP Analytics Cloud Planning Data Action Advance Formula

pragi2028
Explorer
0 Kudos

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

pragi2028_1-1712747284216.png

 

 

Thanks!! in advance

SAP Analytics Cloud SAP Analytics Cloud for planning 

 

View Entire Topic
akhilgs
Explorer
0 Kudos

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
Explorer
0 Kudos

@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

pragi2028_0-1712836993976.png

 

akhilgs
Explorer
0 Kudos

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