cancel
Showing results for 
Search instead for 
Did you mean: 

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

bautayakool
Explorer
0 Kudos

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

 

SAP Blog Question- Cross Referencing Dimension V2.png
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

View Entire Topic
N1kh1l
Active Contributor
0 Kudos

@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:

N1kh1l_0-1709496245808.png 

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

Br.

Nikhil

 

bautayakool
Explorer
0 Kudos
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!