cancel
Showing results for 
Search instead for 
Did you mean: 

Allocation Using Advanced Formula with Parent Nodes as Driver

bautayakool
Explorer
0 Kudos

Hi experts,

We would like to create this outcome where driver and source value has different granularities and uses parent nodes as driver/source. Please refer to below figure. 

bautayakool_0-1712621337648.png

So far, we have been trying this code and it doesn't seem to work. Please advise for how we should debug this script further.

MEMBERSET [d/Measures] = "ZAMOUNT"
MEMBERSET [d/ZALLO_CYCLE] = "Cycle 3"
MEMBERSET [d/ZACTY_TYPE] = BASEMEMBER([d/ZACTY_TYPE], "MACH")
MEMBERSET [d/ZGL_ACCOUNT] = BASEMEMBER([d/ZGL_ACCOUNT].[h/POH_Allocation], "ZAS-C2-005", "ZAS-C2-006", "ZAS-C2-007", "ZAS-C2-008", "ZAS-C2-009", "ZAS-C2-010", "ZAS-C2-011")

VARIABLEMEMBER #ACTY OF [d/ZACTY_TYPE]
VARIABLEMEMBER #CCTR OF [d/ZCCTR]

IF [d/ZCCTR] = %Driver% THEN
	DATA([d/ZACTY_TYPE] = #ACTY, [d/ZGL_ACCOUNT] = "#", [d/Measures] = "ZACTY_QTY") = RESULTLOOKUP([d/Measures] = "ZACTY_QTY", [d/ZALLO_CYCLE] = "Source", [d/ZGL_ACCOUNT] = "#")
	DATA([d/ZACTY_TYPE] = #ACTY, [d/ZGL_ACCOUNT] = "#", [d/ZCCTR] = #CCTR, [d/Measures] = "ZACTY_QTY") = RESULTLOOKUP([d/ZACTY_TYPE] = #ACTY, [d/ZGL_ACCOUNT] = "#", [d/Measures] = "ZACTY_QTY")
	DATA.APPEND([d/ZACTY_TYPE] = "#", [d/Measures] = "ZAMOUNT") = RESULTLOOKUP([d/ZCCTR] = "131210", [d/ZACTY_TYPE] = "#") * RESULTLOOKUP([d/ZACTY_TYPE] = #ACTY, [d/ZGL_ACCOUNT] = "#", [d/Measures] = "ZACTY_QTY") / RESULTLOOKUP([d/ZACTY_TYPE] = #ACTY, [d/ZGL_ACCOUNT] = "#", [d/ZCCTR] = #CCTR, [d/Measures] = "ZACTY_QTY")
ENDIF

Thank you.

Bhumirapee A.

View Entire Topic
N1kh1l
Active Contributor
0 Kudos

@bautayakool 

Your code does not give correct result ? or gives result but against wrong costcenter. I think your code would be working but the end result is getting posted against 131210 rather than against driver costcenter.

Try this if its allocating to source cc instead of driver cc

VARIABLEMEMBER #SRCCCTR OF [d/ZCCTR]

DATA([d/ZCCTR]=#SRCCCTR)=RESULTLOOKUP([d/ZCCTR] = "131210", [d/ZACTY_TYPE] = "#")

Now use this in multiplication with drivers.

DATA.APPEND([d/ZACTY_TYPE] = "#", [d/Measures] = "ZAMOUNT") = RESULTLOOKUP([d/ZCCTR] = #SRCCCTR, [d/ZACTY_TYPE] = "#") * RESULTLOOKUP([d/ZACTY_TYPE] = #ACTY, [d/ZGL_ACCOUNT] = "#", [d/Measures] = "ZACTY_QTY") / RESULTLOOKUP([d/ZACTY_TYPE] = #ACTY, [d/ZGL_ACCOUNT] = "#", [d/ZCCTR] = #CCTR, [d/Measures] = "ZACTY_QTY")

 

Hope this helps !!

Nikhil