Question Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr... in Technology Q&A

SAP Analytics Cloud Planning : Advance Formulas (Aggregation)

SANA11 — Tue, 11 Mar 2025 06:51:34 GMT

Hey all,

can anyone of you help me need to calculate the product of aggregated account dimensions (Units and Lookup Price) across the Product and Time dimensions. However, when I directly multiply these accounts using Data Actions, the calculation is performed row-wise first and then aggregated, leading to incorrect results. Instead, I want to first aggregate the values by Product and Time and then perform the multiplication to ensure accuracy. Can anyone help me with how to achieve this using scripting? I have to claculate the Revenue NA for NorthEast Region

please help this is the code that I wrote just to check th value for units only but no result is appearing

MEMBERSET [d/ENTITY] = "REG0001"
VARIABLEMEMBER #SUMOFUNITS OF [d/ACCOUNT]
DATA([d/ACCOUNT]=#SUMOFUNITS, [d/PRODUCT]="#") = RESULTLOOKUP ([d/ACCOUNT] = "DRV0020" )
DATA([d/ACCOUNT] = "DRV0060" ) = RESULTLOOKUP([d/ACCOUNT] = #SUMOFUNITS , [d/PRODUCT]= "#")

alternative workaround but still values are not appearing

MEMBERSET [d/ENTITY] = "REG0001"
MEMBERSET [d/ACCOUNT] = "DRV0020"
AGGREGATE_DIMENSIONS = [d/PRODUCT],[d/ACCOUNT]
AGGREGATE_WRITETO [d/ACCOUNT] = "DRV0060"
AGGREGATE_WRITETO [d/PRODUCT] = "#"

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggregation)

SANA11 — Tue, 11 Mar 2025 07:39:07 GMT

anybody ? will appreciate your response

N1kh1l please

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggregation)

MoonJun — Tue, 11 Mar 2025 10:16:51 GMT

Hi @SANA11

The meaning of "the calculation is performed row-wise first and then aggregated," seems to be below the calculation,

105,082.00	*	10	=	1,050,820.00
178,510.00	*	12	=	2,142,120.00
9,459.00	*	8	=	75,672.00
				-
891,634.00	*	35	=	31,207,190.00
243,333.00	*	30	=	7,299,990.00

		Aggregate Total:		41,775,792.00

What do you want to get your expected results "I want to first aggregate the values by Product and Time and then perform the multiplication to ensure accuracy. "?

Is the below calculation?

105,082.00		10
178,510.00		12
9,459.00		8

891,634.00		35
243,333.00		30

1,428,018.00	*	95	=	135,661,710.00
Aggregated Qty		Aggregate Price

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

MKreitlein — Tue, 11 Mar 2025 12:52:58 GMT

Hello @SANA11

have you checked all the examples given in the SAC Help pages?

E.g. https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/78d550c8cb154c25b8e73810fabcddbc.html

There you can find some examples around the topic.

BR, Martin

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Tue, 11 Mar 2025 15:12:21 GMT

@MoonJun yes the below one calculate the total first and then take the product

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Tue, 11 Mar 2025 15:15:57 GMT

MKreitlein so, I have used the formula bar and formula works fine in that formula bar, but when I do it using data action scripting it's not providing me the results though as per my understanding the results have to be the same no matter which approach one is using

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

MKreitlein — Tue, 11 Mar 2025 15:31:37 GMT

I did something similar, but the code will not match here... so just the idea in words: I guess you should create a temporary key figure on one specific date e.g. 12.2025 with dimension restriction PRODUCT = # and then you can use this temporary key figure for further calculation. BR, Martin

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Tue, 11 Mar 2025 17:02:57 GMT

MKreitlein can you please explain it a bit interms of code what do you mean by key figure appreciate your response

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Wed, 12 Mar 2025 04:54:27 GMT

MoonJun Can you suggest how I can achieve the desired result using the second approach? Additionally, should # be used for aggregation against the product dimension, given that the screenshot shows the product hierarchy maintained in the system

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

MoonJun — Wed, 12 Mar 2025 05:45:37 GMT

Hi @SANA11

Please refer to the Advanced formulas script and validate the calculated result.

MEMBERSET [d/Entity] = "REG0001" VARIABLEMEMBER #All_Product OF [d/PRODUCT] //Aggregate "Units" & "Lookup_Price" of Product and write it on VARIABLEMEMBER #All_Product DATA([d/PRODUCT] = #All_Product) = RESULTLOOKUP() //Calculate Revenue to multiply Aggregated "Units" & "Lookup_Price" of Product and write it on Product "#" and Account "Revenue" DATA([d/PRODUCT] = "#", [d/Account] = "Revenue") = RESULTLOOKUP([d/PRODUCT] = #All_Product, [d/Account] = "Units") * RESULTLOOKUP([d/PRODUCT] = #All_Product, [d/Account] = "Lookup_Price")

I don't have an idea about the Account dimension Member ID, so please replace it with your actual Account dimension Member ID, which is Account dimension member description is listed in my script.

I hope this is helpful to you, and if I have misunderstood anything, please feel free to reach out.

moonjun

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Wed, 12 Mar 2025 06:26:33 GMT

MoonJun appreciating your response I have executed the code but still incorrect value is appearing

MEMBERSET [d/ENTITY] = "REG0001"
MEMBERSET [d/ACCOUNT] = ("DRV0020","DRV0055")
VARIABLEMEMBER #All_Product OF [d/PRODUCT]
DATA([d/PRODUCT] = #All_Product) = RESULTLOOKUP()
DATA([d/PRODUCT] = "#", [d/ACCOUNT]= "DRV0060") = RESULTLOOKUP([d/PRODUCT] = #All_Product, [d/ACCOUNT] = "DRV0020") *
RESULTLOOKUP([d/PRODUCT] = #All_Product, [d/ACCOUNT] = "DRV0055")

the actual value has to be 103,170,905,160 please assist

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

MoonJun — Wed, 12 Mar 2025 06:42:41 GMT

Hi @SANA11

Please let me know all dimensions of your Model. It seems to be more dimensions and the current aggregate value has a dimensionality. To get your expected result, the other dimensions should also be aggregated.

Regards,

Moonjun.

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Wed, 12 Mar 2025 07:25:28 GMT

MoonJun these are the dimensions product, time, flow, audit, and entity but I have to calculate against northeast only so have applied filter on entity please assist

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

MoonJun — Wed, 12 Mar 2025 08:52:18 GMT

Hi @SANA11

Please refer to the below script.

It seems that aggregating only the DATE dimension would be sufficient to achieve the desired result, but the Audit and Flow dimensions were also aggregated.

MEMBERSET [d/Entity] = "REG0001" VARIABLEMEMBER #All_Product OF [d/PRODUCT] //Aggregate "Units" & "Lookup_Price" of Produdt, Flow, Audit and Date members and write it on VARIABLEMEMBER #All_Product DATA([d/PRODUCT] = #All_Product, [d/Date] = "201801", [d/FLOW] = "#", [d/AUDIT] = "#") = RESULTLOOKUP() //Calculate Revenue to multiply Aggregated "Units" & "Lookup_Price" of Product and write it on Product "#" and Account "Revenue" DATA([d/PRODUCT] = "#", [d/Date] = "201801", [d/FLOW] = "#", [d/AUDIT] = "#", [d/Account] = "DRV0060") = RESULTLOOKUP([d/PRODUCT] = #All_Product, [d/Date] = "201801", [d/FLOW] = "#", [d/AUDIT] = "#", [d/Account] = "DRV0020") * RESULTLOOKUP([d/PRODUCT] = #All_Product, [d/Date] = "201801", [d/FLOW] = "#", [d/AUDIT] = "#", [d/Account] = "DRV0055")

Regards,

Moonjun

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Wed, 12 Mar 2025 10:04:34 GMT

MoonJun Since my data spans two years, 2018 and 2019, wouldn't selecting data from January 2018 (201801) impose a restriction on the results cause summation is of two years. would appreciate your response

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Thu, 13 Mar 2025 05:25:43 GMT

MoonJun, could you please assist with the date dimension issue? Even when executing the code, it doesn't return the expected result for 201801; instead, it provides an aggregated result.

Re: SAP Analytics Cloud Planning : Advance Formulas (Aggr...

SANA11 — Thu, 13 Mar 2025 07:31:54 GMT

is there anyone who can guide ?