Aggregation on Data Action

[SANA11]

Hello all, 

Can anyone help me resolve the issue I'm facing? I need to calculate the value of Revenue NA [DRV0060] by multiplying the lookup price and units, but the resulting values are incorrect. I'm implementing this using scripting in a data action, and the values appearing are significantly lower than the expected ones, which are simply obtained by multiplying the two factors. I suspect the issue is due to improper aggregation at the hierarchical level. 

Script I am using 
DATA([d/ACCOUNT] = "DRV0060") =
RESULTLOOKUP([d/ACCOUNT] = "DRV0055") * RESULTLOOKUP([d/ACCOUNT] = "DRV0020")

below are the incorrect values 

[ihar_stankevich]

In Data Action you could use AGGREGATE_DIMENSIONS, AGGREGATE_WRITETO functions. But in this case end calculation will be on WRITETO members

[William_Yu1]

If both your accounts "DRV0055" and "DRV0020" are leaf member and not calculated account, I don't thinkt it's a problem of aggregation(of DA calculation).  Calculation is done on the lowest combinations of all other dimensions with both accounts. I would suggest you to add all your dimensions into the table grid to understand the calculate result.  I assume not all the bases of displayed(aggregated on non displayed dimensions) value accounts not having same combination.

[SANA11]

appreciating your response holger.handel william.yu derekl.johnson you mean to say account dimension I have to check the impact on all account dimensions right ?

[William_Yu1]

NO, check all dimension combination for both accounts.

[SANA11]

holger.handel william.yu derekl.johnson I don't get your point can you please explain or share screenshot so that could understand 

It means that not all base values contributing to the displayed (aggregated) value share the same combination of non-displayed dimensions.

[SANA11]

Can anyone assist me? I am unable to identify the root cause of this issue and am unsure what is going wrong. Below is the structure of my model for reference.

Additionally, for the Northeast region, an incorrect aggregation result is appearing at the parent hierarchy node. Moreover, currency conversion is being applied to the Units account dimension, even though it is disabled in the properties.

@N1kh1l  please assist 

[William_Yu1]

Please put dimension FLOW and AUDIT also into your table , make all dimension show leaf only and check the data before DA calculation

[SANA11]

 William_Yu   

added all leaf's that you have asked in table grid please suggest what would be the next step 

[William_Yu1]

You should show leaf only for account, time and product in row axis for better understanding of your data as well.

[SANA11]

William_Yu should I run data action on it now and check the impact on values that will be changed 

[William_Yu1]

Check your data if value of unit account match value of price account for all dimensions, then you can execute and check result .

[SANA11]

William_Yu  How can the values of the Unit account and Price account be the same, given that the price is calculated using the formula: DRV0055 Lookup Price (NA) = LOOKUP([DRV0050], [d/ENTITY] = "#") Since the Lookup Price for the United States is marked as unbooked data, I copied the value from Entity #, which is 2280, but when calculating manually (using excel sheet), the accumulated value against the dimension appears as 2215. This discrepancy seems to be due to footwear data not appearing for December 2019. However, when using a numeric point chart, the value is displayed accurately.. This discrepancy suggests that the lookup might not be retrieving values correctly across all dimensions, or some form of aggregation is affecting the final result. Could it be that certain records are missing or being aggregated differently? scripting: interms of scripting have used the following formula //DATA([d/ACCOUNT] = "DRV0055",[d/ENTITY] = "REG0001") = RESULTLOOKUP([d/ACCOUNT] = "DRV0050", [d/ENTITY] = "#")

[SANA11]

William_Yu please assist your response will be appreciated

[William_Yu1]

It's not about matching value it's about matching the data point.

[SANA11]

William_Yu can you please explain via screen shot or something I didn't get it

[William_Yu1]

n/a

[William_Yu1]

I would suggest you put two source accounts and target account into column axis, and rest of the dimensions into row axis, you can easily find out if multiply two accounts will have result. Or how should specify different conditions in RESULTLOOKUP like [d/Entity] = "#".

[SANA11]

William_Yu please confirm if doing it rightly or not cause I am doing it for North East Entity only to not create a mess so I take only one entity at a time 

[SANA11]

@William_Yu1, I’ve identified the root cause. The actual result should be the product of the sum of units and the sum of lookup price. However, the inaccurate value appears because the calculation is performed row-wise, multiplying units by lookup price for each dimension and then summing the results, which leads to incorrect aggregation. Can you help me correct this issue? How can I ensure the calculation follows the correct aggregation logic in SAP Analytics Cloud?

[SANA11]

William_Yu appreciate your response in relation to how it will be rectified

[William_Yu1]

Though I'm not sure why the multiply should be after aggregation, but if that's the logic, should try to use variable in advance formula to get the aggretated result first.

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/afe93e3cf1414a7b8419b...

[ihar_stankevich]

Hi, 

RESULTLOOKUP()*RESULTLOOKUP() - it's multiplication of the same booked combinations from both accounts. 

Add context (all dimensions) to both of your Accounts (DRV0055 and DRV0020). Only identical combinations from these accounts will be multiplied.

Multiplication is happening on the row level.

In your case, you could define your formula (multiplication) in Account dimension in Formula field. In this case in report - SAC aggregate all dimensions and after will apply multiplication. 

More info you could find here https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/ce7b148eeac942f6ad1d0...

Also aggregated result could be achieved through calculated measure. The question what is the end goal. In these both options calculation will be on fly

Best regards, 

[SANA11]

holger.handel william.yu derekl.johnson can you please assist in this manner whether account dimension formula field is the only option or we can do it with data actions scripting as well