SAC Planning - Advanced formula - LINK

[daniele_cortopassi]

Hello Experts, I've to manage a calculation with Advanced formulas.

I've operator 1 (Quantity) on Default model, I've operator 2 (COGS per unit) on Linked model

I've to write the results of Operator 1 * Operator 2 on Default model, but some dimension member value must be driven from Operator2: for example Partner company and currency

for Currency I can use a masterdata relationship, but for Company how can I override the source member with operator2 member? if I don't specify a value for Partner company the records are created for all the partner companies

DATA([d/Measures] = "M_AMOUNT", [d/ACCOUNT] = "C_COGS", [d/Currency]  = [d/Company].[p/currency], [d/AS_PCOMPANY] = ???) = RESULTLOOKUP([d/Measures] = "M_QTY", [d/ACCOUNT] = "T_QTY") * LINK([OPERATOR2], [d/Version] = "public.CurrentPlan", [d/Measures] = "M_AMOUNT")

I've simplified the case, Operator1 contains many other dimensions that are correct for the result and therefore should be the left member of the calculation.do you have any suggestion how to handle this problem? thanks for your collaboration.Kind regards

[hartmut_koerner · Accepted solution]

Hi Daniele,

the trick is the following: As you might not know the partner company and the currency of the operator 1, you first you need to copy/aggregate this value to a dedicated member. I typically use variablemembers for that, because I don't want this intermediate result to be stored afterwards.
In the next step you do the multiplication where you address the members for the operator 1 with the dedicated variablemembers and for operator 2 you simply do not specify the partner company and currency. In this situation, the system automatically takes over the member values from operator 2.

VARIABLEMEMBER #1 OF [d/AS_PCOMPANY]
VARIABLEMEMBER #2 OF [d/Currency]

DATA([d/Measures] = "M_QTY", [d/ACCOUNT] = "T_QTY", [d/AS_PCOMPANY] = #1, [d/Currency] = #2) = 
RESULTLOOKUP([d/Measures] = "M_QTY", [d/ACCOUNT] = "T_QTY")

DATA([d/Measures] = "M_AMOUNT", [d/ACCOUNT] = "C_COGS") = 
RESULTLOOKUP([d/Measures] = "M_QTY", [d/ACCOUNT] = "T_QTY", [d/AS_PCOMPANY] = #1, [d/Currency] = #2) * 
LINK([OPERATOR2], [d/Version] = "public.CurrentPlan", [d/Measures] = "M_AMOUNT", [d/ACCOUNT] = "T_COGS")

Best regards
Hartmut

[daniele_cortopassi · Accepted solution]

Thanks Hartmut your answer was perfect, you made my day. Cheers!