SAC Data Action: Advanced Formula aggregation issue,...

[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