SAP Analytics Cloud Planning: Advanced formula to Cross Reference Other Dimension

[bautayakool]

Hi experts, 

Scenario: I would like to generate cost of a multi-level FG, where there is a cost from materials and activity, as displayed here.

Edit: Adjust figure to display technical names of each dimension

In this specific scenario, I would like to get planned price (Amount) for the FG which has "X" marked on it. The planned price can be derived by referencing to another dimension which has identical ID.

So far, I have tried this code, but to no effects. 

"

CONFIG.TIME_HIERARCHY = CALENDARYEAR
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = OFF
CONFIG.GENERATE_UNBOOKED_DATA = ON

VARIABLEMEMBER #Temp2 OF [d/ZPRODUCT_V2]
// Store Calculated Value in a Temp member

AGGREGATE_DIMENSIONS = [d/ZACCOUNT_TEST], [d/ZACTIVITY_TYPE]
AGGREGATE_WRITETO [d/ZACCOUNT_TEST] = "5110002"
AGGREGATE_WRITETO [d/ZACTIVITY_TYPE] = "#"
//AGGREGATE values into these member

DATA([d/ZASSEMBLY] = "X", [d/ZPRODUCT_V2] = #Temp2, [d/Measures] = "Amount") = RESULTLOOKUP([d/ZPRODUCT] = [d/ZPRODUCT_V2], [d/Measures] = "Amount") / RESULTLOOKUP([d/ZPRODUCT] = [d/ZPRODUCT_V2], [d/Measures] = "Quantity")
DATA([d/ZASSEMBLY] = "X", [d/Measures] = "Amount") = RESULTLOOKUP([d/ZPRODUCT_V2] = #Temp2, [d/Measures] = "Amount")
//Calculation & Write data

"

I would like your guidance on whether this scenario is supported and if so, how can i achieve my expected result. Thank you.

Bhumirapee Autayakool

[N1kh1l · Accepted solution]

@bautayakool 

Sorry for late response, I had to create a similar model to better understand the product swap. The below code will do the trick.

VARIABLEMEMBER #Temp2 OF [d/ZPRODUCT_V2]
VARIABLEMEMBER #P OF [d/ZPRODUCT]

AGGREGATE_DIMENSIONS = [d/ZACTIVITY_TYPE],[d/ZACCOUNT_TEST]
AGGREGATE_WRITETO [d/ZACCOUNT_TEST] = "5110002"
AGGREGATE_WRITETO [d/ZACTIVITY_TYPE] = "#"

DATA( [d/ZPRODUCT_V2] = #Temp2, [d/Measures] = "Amount") = RESULTLOOKUP( [d/Measures] = "Amount") / RESULTLOOKUP( [d/Measures] = "Quantity")
DATA([d/ZPRODUCT]=#P)=RESULTLOOKUP([d/ZPRODUCT]=[d/ZPRODUCT_V2],[d/ZPRODUCT_V2] = #Temp2, [d/Measures] = "Amount")
DATA([d/Measures] = "Amount") = (RESULTLOOKUP([d/Measures]="Quantity")/RESULTLOOKUP([d/Measures]="Quantity"))* RESULTLOOKUP([d/ZPRODUCT]=#P, [d/Measures] = "Amount",[d/ZASSEMBLY]="#")

Output:

 

Hope this helps. Please close the thread by accepting the helpful answer.

Br.

Nikhil

[bautayakool · Accepted solution]

I have tried your code and it worked! I will try to test this code with some other test cases, but I think I can continue on from here. Thank you very much for you help!

[N1kh1l]

@bautayakool 

You should show full dimension headers of the table and also tell us technical id of the dimensions. Looking at your table and scripts its difficult to know which dimension is material and which is BoM. The reason its not working is as the data are on different boM. Aggregate the last 2 records on BoM using variable member which will give you 20/100 and 20/100

DATA( [d/ZPRODUCT_V2] = #Temp2, [d/Measures] = "Amount") = RESULTLOOKUP( [d/Measures] = "Amount") / RESULTLOOKUP( [d/Measures] = "Quantity")

DATA([d/ZASSEMBLY] = "X", [d/ZACCOUNT_TEST] = "5110002", [d/Measures] = "Amount") = (RESULTLOOKUP()/RESULTLOOKUP())* RESULTLOOKUP([d/ZPRODUCT]=[d/ZPRODUCT_V2],[d/ZPRODUCT_V2] = #Temp2, [d/Measures] = "Amount")

Nikhil

[bautayakool]

I apologize for using the description instead of the ID for the figure. I have adjusted the figure to display ID instead.

I have tried using your script, and unfortunately, it still doesn't provide the result I was looking for. (no change to the target version)