SAP Analytics Cloud for planning - Data Action for Aggregation on Dimension Property

[guenay]

Hi everyone,

In the table below, you'll see values associated with the account member COMA032_%. The dimensions involved are PLG1 (Property of Material) and Material. I have multiple PLG1 properties, and I’d like to aggregate (sum) all values under COMA032_% and write the result to a new account member called COMA032_%_Material.

This new account member should show the sum of values per PLG1, aggregated across all Materials.

Has anyone written a data action to achieve something similar? I’ve attempted a solution but haven’t been able to get it working correctly.

Kind regards,

Ismail

[MoonJun]

Hi @guenay

PLG1 and the Material dimension are different dimensions, and the property of PLG1 doesn't affect value aggregation, correct?

Moonjun

[guenay]

In this Case we have Material as Dimension and PLG1 ist a Property of this Material Dimension.

[MoonJun]

Hi @guenay

Does that mean the Material dimension is configured as shown in the screenshot below?

Moonjun

[guenay]

Yes, exactly!

[MoonJun · Accepted solution]

Hi @guenay 

Please refer to the below Advanced Formulas script to get your expectation. 

MEMBERSET [d/Measures] = "Amount"
MEMBERSET [d/Date] = "202501"

VARIABLEMEMBER #PLG1_total OF [d/Material]

FOREACH [d/Material].[p/PLG1]
	DATA([d/Account] = "COMA032_%", [d/Material] = #PLG1_total)
	= RESULTLOOKUP([d/Account] = "COMA032_%")
	
	IF RESULTLOOKUP([d/Account] = "COMA032_%") != Null THEN
		DATA([d/Account] = "COMA032_%_Material")
		= RESULTLOOKUP([d/Account] = "COMA032_%", [d/Material] = #PLG1_total)
	ENDIF
ENDFOR

This is an AF script logic that temporarily stores subtotals by property in the variable member #PLG1_total, and then records those subtotals into the account "COMA032_%_Material" and the corresponding Material member.

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

Moonjun

[guenay · Accepted solution]

Thank you very much moonjun. It works perfectly.