Question Re: Aggregation on Data Action in Technology Q&A

Aggregation on Data Action

SANA11 — Fri, 07 Mar 2025 08:01:17 GMT

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

Re: Aggregation on Data Action

ihar_stankevich — Sat, 08 Mar 2025 08:26:12 GMT

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/ce7b148eeac942f6ad1d076d73d1e6fd.html?locale=en-US

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,

Re: Aggregation on Data Action

SANA11 — Sat, 08 Mar 2025 07:07:46 GMT

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

Re: Aggregation on Data Action

William_Yu1 — Sat, 08 Mar 2025 08:22:17 GMT

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.

Re: Aggregation on Data Action

ihar_stankevich — Sat, 08 Mar 2025 09:49:46 GMT

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

Re: Aggregation on Data Action

SANA11 — Sat, 08 Mar 2025 19:06:19 GMT

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 ?

Re: Aggregation on Data Action

William_Yu1 — Sun, 09 Mar 2025 01:33:46 GMT

NO, check all dimension combination for both accounts.

Re: Aggregation on Data Action

SANA11 — Sun, 09 Mar 2025 15:03:50 GMT

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.

Re: Aggregation on Data Action

SANA11 — Mon, 10 Mar 2025 04:08:05 GMT

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

Re: Aggregation on Data Action

William_Yu1 — Mon, 10 Mar 2025 04:26:14 GMT

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

Re: Aggregation on Data Action

SANA11 — Mon, 10 Mar 2025 05:07:23 GMT

William_Yu

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

Re: Aggregation on Data Action

William_Yu1 — Mon, 10 Mar 2025 05:01:19 GMT

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

Re: Aggregation on Data Action

SANA11 — Mon, 10 Mar 2025 05:22:21 GMT

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

Re: Aggregation on Data Action

William_Yu1 — Mon, 10 Mar 2025 05:38:07 GMT

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

Re: Aggregation on Data Action

SANA11 — Mon, 10 Mar 2025 06:20:46 GMT

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

Re: Aggregation on Data Action

SANA11 — Mon, 10 Mar 2025 06:49:15 GMT

William_Yu please assist your response will be appreciated

Re: Aggregation on Data Action

William_Yu1 — Mon, 10 Mar 2025 07:05:39 GMT

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

Re: Aggregation on Data Action

SANA11 — Mon, 10 Mar 2025 07:11:48 GMT

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

Re: Aggregation on Data Action

William_Yu1 — Mon, 10 Mar 2025 07:19:26 GMT

n/a

Re: Aggregation on Data Action

William_Yu1 — Mon, 10 Mar 2025 07:18:54 GMT

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