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

this is what I try to achieve:

Thank you
Deb