Skip to Content
0
Sep 08, 2023 at 01:13 PM

Advanced formula for allocation with with multiple hierarchies

163 Views

How can I create an Advanced formula to create the bellow distribution logic.

I want to enter planning data for a dimension with multiple hierarchies - both to parent node and leaves.

in my case the Parent node planning should include the leaf. therefore I've created 3 hierarchies - see below print screen from SAC.

1. the first one holds only "to be planned" members (attribute "Green")

2. the second "intermediate hierarchy" holds everything but the "planned SKUs" (the parent-node is in an alternative parent). In this hierarchy, I want to proceed with an allocation, with actual as driver with an advanced formula. I need to do it separately as the parents plan shouldn't be affected with the actual ratio .The formula should perform the below calculation:

Actual sum in leaves divided by total actual in intermediate Hierarchy multiplied by the budget sum in alternate parent.

the thing is that I struggle writing this down. I begun with the bellow, but I miss something:

DATA([d/PLM_PRODUCTS]=[d/PLM_PRODUCTS].[p/ALTERNATE_ATT])=
RESULTLOOKUP([d/PLM_PRODUCTS]=[d/PLM_PRODUCTS].[p/ALTERNATE_ATT],[d/Version]="public.Actual")/
????? *
RESULTLOOKUP([d/PLM_PRODUCTS]=[d/PLM_PRODUCTS].[p/SOURCE_PRNT_ATT])<br>

3. then I should see the result in my "full business hierarchy" that includes all leaves.

Thanks again for your help

this is SAC

image.png

this is what I try to achieve:

image.png

Thank you

Deb

Attachments

image.png (136.9 kB)
image.png (100.5 kB)