SAP Analytics Cloud Aggregation with DATA function

[masa94]

Hi SAP Gurus,

I took an exercise of openSAP course "Planning with SAP Analytics Cloud" and face 1 issue.

In the Week2 exercise 8, The instruction introduces to write below advanced formula.

MEMBERSET [d/MEASURE] = "GROSS_SALES"
MEMBERSET [d/SAP_CEP_LEVEL] = "#"
MEMBERSET [d/Date] =  "202101" TO "202112"

DATA([d/SAP_CEP_PRODUCT]=[d/SAP_CEP_PRODUCT].[p/ROLLUP],[d/SAP_CEP_LEVEL]="PRDGRP") = 
	RESULTLOOKUP([d/Version]="public.Actual",[d/Date]=PREVIOUS(12))

The formula tries to copy GROSS_SALES data from Version "Actual" to "Aggregate".

And Instead of using original member IDs, create record with values in Property "Roll-up".

However, I suppose it is impossible because values in "Roll-up" are root members.

According to Advanced Formulas Reference Guide, member_id in DATA function should be a leaf member.

Advanced Formula Reference Guide DATA function

Anybody completed this exercise and how did you solve it?

[N1kh1l]

masa94

In SAC RESULTLOOKUP can not read aggregated values of hierarchy nodes on the fly in Advanced formula. To workaround this what that code is doing is aggregating Gross Sales Value of each product from last year periods and storing it on Rollup attribute value members like ZMTN, ZCRUISE etc. So all products belonging to these attributes will be aggregated and persisted in data base.

Hierarchy3 makes everything plannable as everything is root member according to that.

for e.g. If all product under ZCRUISE has a value of 100 in 2020.01 this code will generate below

Same will done for all periods and node members like ZMTN , ZYOUTH etc. Product Hierarchy and Rollup attribute have same values for the product members.

Also you will notice that all these node members (Rollup attribute values) are also grouped in second product hierarchy2. So essentially this code will summarize last year Gross Sales of individual products by attribute values and copy it to current year periods. Also notice the differentiation of these aggregated records by dimension SAP CEP LEVEL = PRDGRP. All individual product members were on SAP CEP LEVEL = "#".

This helps in doing further calculation on aggregated Gross sales numbers like calculating Average Gross Sales etc. Also this will come handly when you want to transfer Gross Sales number to another model through Cross Model copy or LINK function.

Hope this helps you.

Please upvote/Accept if you find this helpful.

Regards

Nikhil

[N1kh1l]

holger.handel william.yu derekl.johnson

I observed that the aggregation by attribute now only works if the attribute value is an existing leaf member. But before it used to work even if the attribute used to be an existing parent node provided there is another hierarchy which has these nodes as leaf member. The Advanced formula does not throw error but it simply generated no records. So this below code from OpenSAP Exercise does not work anymore. It only works if there is a leaf member in ROLLUP attribute.

MEMBERSET [d/MEASURE] = "GROSS_SALES"
MEMBERSET [d/SAP_CEP_LEVEL] = "#"
MEMBERSET [d/Date] =  "202101" TO "202112"

DATA([d/SAP_CEP_PRODUCT]=[d/SAP_CEP_PRODUCT].[p/ROLLUP],[d/SAP_CEP_LEVEL]="PRDGRP") = 
	RESULTLOOKUP([d/Version]="public.Actual",[d/Date]=PREVIOUS(12))

Could you please advise if there is a change in behavior now. This used to be crucial for a cross model transfer.

Regards

Nikhil

[William_Yu1]

No, it never works on parent node. Any member specified in the DATA() need to be leaf member of specified hierarchy.