Allocation of multiple hierarchies from parent node to child nodes

[Freddie]

Hello SAC Community,

I have combined and mapped two different account hierarchies into one with budget accounts being the parent of the regular G/L Accounts.

On the budget accounts, they type a top-down number for internal allocations. These allocations needs to be allocated from the parent (budget account) to the child nodes (G/L Acccounts). It looks like below:

I need to allocate the -1000 on 9010BU to the below G/L account child nodes based on the distribution of the Cost Type = Original. Furthermore, it needs to be done for every member in the Cost Center dimension.

I have tried the below code, where I have made an attribute called "Allocation" in the Account dimension "ACCOUNT_GL_BU" filled with some text for the child nodes (G/L Accounts) of the "Internal Shares" budget accounts (G/L Accounts).

MEMBERSET [d/Measures] = "COST"

MEMBERSET [d/Date] = [d/Version].[p/START_DATE_PLAN] TO [d/Version].[p/END_DATE_PLAN]

MEMBERSET [d/HUSQ_ACCOUNT_GL_BU] = BASEMEMBER([d/HUSQ_ACCOUNT_GL_BU].[h/BU_HIERARCHY], "BU_ACCOUNT_PLAN")

 MEMBERSET [d/HUSQ_COST_TYPE] = ("INTERNAL_ALLOC", "ORIGINAL")

VARIABLEMEMBER #AGGREGATED_ORIGINAL OF [d/HUSQ_ACCOUNT_GL_BU]

VARIABLEMEMBER #AGGREGATED_ALLOCATION OF [d/HUSQ_ACCOUNT_GL_BU]

IF [d/HUSQ_ACCOUNT_GL_BU].[p/Allocation] != "" THEN

DATA([d/HUSQ_ACCOUNT_GL_BU] = #AGGREGATED_ORIGINAL) = RESULTLOOKUP([d/HUSQ_COST_TYPE] = "ORIGINAL", [d/Version] = "public.Final Allocation")

DATA([d/HUSQ_ACCOUNT_GL_BU] = #AGGREGATED_ALLOCATION) = RESULTLOOKUP([d/HUSQ_COST_TYPE] = "INTERNAL_ALLOC", [d/Version] = "public.Final Allocation")

 DATA([d/HUSQ_ACCOUNT_GL_BU] = #AGGREGATED_ALLOCATION) = RESULTLOOKUP([d/HUSQ_COST_TYPE] = "INTERNAL_ALLOC", [d/Version] = "public.Final Allocation")

DATA([d/HUSQ_COST_TYPE] = "INTERNAL_ALLOC") = (RESULTLOOKUP([d/HUSQ_COST_TYPE] = "ORIGINAL", [d/Version] = "public.Final Allocation") / RESULTLOOKUP([d/HUSQ_ACCOUNT_GL_BU] = #AGGREGATED_ORIGINAL)) * RESULTLOOKUP([d/HUSQ_ACCOUNT_GL_BU] = #AGGREGATED_ALLOCATION)

ENDIF

And finally here is an excel file of the idea:

Any help is appreciated!

[N1kh1l]

@Freddie 

I think you should improve your excel screenshot by also showing the Allocation attribute of the accounts. The reason I ask this is because Its not clear whether you use total of all accounts as denominator for allocation weight or only allocation accounts. To me it looks like you want to allocate internal allocation number based final allocation weights.

My question is how did you manage to enter -1000 on parent node 9010BU. You would need either a dummy leaf member to store the total allocation amount -1000. Or you would need to create an alternate hierarchy.

Your code will just change here assuming you will use DUMMY_ACCOUNT member to store -1000

DATA([d/HUSQ_COST_TYPE] = "INTERNAL_ALLOC") = (RESULTLOOKUP([d/HUSQ_COST_TYPE] = "ORIGINAL", [d/Version] = "public.Final Allocation") / RESULTLOOKUP([d/HUSQ_ACCOUNT_GL_BU] = #AGGREGATED_ORIGINAL)) * RESULTLOOKUP([d/HUSQ_ACCOUNT_GL_BU] = "DUMMY_ACCOUNT")

Hope it helps !!

Nikhil

[Freddie]

Hello n1kh1l.

I have combined af model of GL accounts and BU accounts into this model. The -1000 is imported directly into BU9010. Then I created this hiearchy and need to allocate these -1000 to the GL accounts. Hope it make sense?

[Freddie]

This doesn't seem to work...