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 

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

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)