SAC Data Action: Advanced Formula aggregation issue, values summing up

[MoonJun]

Dear Community, i am facing an issue with a SAC Data Action using Advanced Formulas for revenue calculation.

I want to compare two measures with the names "Umsatzmiete_VZ" and "Umsatzmiete" for a list of shops and write the higher value into the target measure "Tatsächlicher Erlös". When using a standard IF-ELSE statement, instead of picking the higher value, SAC evaluates the condition line-by-line across different underlying dimensions (e.g., Profit Centers / Contracts) and ends up **adding/summing up** the values into `Tatsaechlicher_Erloes`.

Code Snippet: 

// 1. Target definition
MEMBERSET [d/Measures] = "Tatsaechlicher_Erloes"
MEMBERSET [d/Datum] = BASEMEMBER([d/Datum].[h/YQM], %Planjahr%)
MEMBERSET [d/Shopbezeichnung] = "Binder Optik GmbH"

// 2. Vectorial IF-ELSE Logic (causes aggregation/summing issue)
IF RESULTLOOKUP([d/Measures] = "Umsatzmiete_VZ") >= RESULTLOOKUP([d/Measures] = "Umsatzmiete") THEN
DATA() = RESULTLOOKUP([d/Measures] = "Umsatzmiete_VZ")
ELSE
DATA() = RESULTLOOKUP([d/Measures] = "Umsatzmiete")
ENDIF

How can I perform a proper aggregated comparison between two measures without running into summing up values improperly?

Any help or best practices would be greatly appreciated!

Best regards, Nico

[MoonJun]

Hi @Hoppeno

When using "Write Higher Value into the Target Measure Tatsächlicher Erlös," is the value written at the aggregated level of the Profit Centers / Contracts dimension, or is it written to the leaf-level members?

Regards,

Moonjun

[MoonJun]

Hi @Hoppeno 

For the Profit Center and Contract dimensions, use Variable Members to perform the aggregation. Then, compare the temporary values stored in the Variable Members to derive the desired result. Please refer to the Advanced Formulas script below.

MEMBERSET [d/Measures] = "Tatsaechlicher_Erloes"
MEMBERSET [d/Datum] = BASEMEMBER([d/Datum].[h/YQM], %Planjahr%)
MEMBERSET [d/Shopbezeichnung] = "Binder Optik GmbH"

VARIABLEMEMBER #V_Umsatzmiete_VZ OF [d/Measures]
VARIABLEMEMBER #V_Umsatzmiete OF [d/Measures]

DATA([d/Measures] = #V_Umsatzmiete_VZ, [d/Profit_Centers] = "#", [d/Contracts] = "#") = RESULTLOOKUP([d/Measures] = "Umsatzmiete_VZ")
DATA([d/Measures] = #V_Umsatzmiete, [d/Profit_Centers] = "#", [d/Contracts] = "#") = RESULTLOOKUP([d/Measures] = "Umsatzmiete")

IF RESULTLOOKUP([d/Measures] = #V_Umsatzmiete_VZ, [d/Profit_Centers] = "#", [d/Contracts] = "#")
	- RESULTLOOKUP([d/Measures] = #V_Umsatzmiete, [d/Profit_Centers] = "#", [d/Contracts] = "#") >= 0 THEN
	DATA() = RESULTLOOKUP([d/Measures] = "Umsatzmiete_VZ")
ELSE
	DATA() = RESULTLOOKUP([d/Measures] = "Umsatzmiete")
ENDIF

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

Regards,

Moonjun

[vinay_009]

@Hoppeno ,

The summing happens because Advanced Formulas calculates at leaf level for any dimension you haven't explicitly restricted or aggregated (e.g. Profit Center, Contract). So your IF/RESULTLOOKUP comparison isn't running once per shop, it's running separately for every Profit Center/Contract combination, and each of those writes its own result. 

Try fixing it with adding Aggregate_dimensions / Aggregate_writeto - this helps us to aggregate those dimensions before comparing and write the single result to one member instead of each leaf.

Swap in whatever dimensions your model has that aren't already set in a MEMBERSET. If you skip one, it still gets calculated leaf by leaf, causing the same summing issue. Once you aggregate it properly, the comparison works on the totals , so it picks the higher value instead of adding everything together.

Thanks,

Vinay