SAP Analytics Cloud Planning : Advance Formulas (Aggregation)

[SANA11]

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] = "#"

[SANA11]

anybody ? will appreciate your response

N1kh1l please 

 

[MoonJun]

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

[MoonJun]

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

[SANA11]

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

[MoonJun]

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. 

[SANA11]

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 

[MoonJun]

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

[SANA11]

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 

[SANA11]

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.

[SANA11]

is there anyone who can guide ?