Child and Parent mixed level formula
We've come up against an issue we're struggling to resolve.
The situation is we have 1 Parent and 2 Children.
We have the TotalStaff at the Parent level.
Child 1 is prioritised and may require 60 of the staff to meet its SLAs - this is determined in a second line item.
What we would like to do is be able to calculate the staff available for Child 2 (and downstream calculations using this) defined as (PARENT - CHILD1)
We were trying to do something like the attached screenshot - alternatively feel free ot tell me this is wrong and there's a much better alternative!
Answers
-
Hello,
You can use the rankcumulate function : https://help.anaplan.com/4727e9c5-4687-45ec-a4a0-420d30476d42-RANKCUMULATE
Create one "priority" line item per child and one "need" line item per child, and use it for your rankcumulate :
Child 1 Child 2 Child 3 Priority 1 2 3 Need 60 50 40 Rankcumulate 60 110 150 allocation (50) 50 0 0 allocation (120) 60 50 10 Thanks to that, you will be able to have the allocation with a formula like MIN(Need, MAX(TotalStaff - Rankcumulate + Need,0))
I hope this is what you wanted to do
2 -
@RickyTillson @MathisG, I am still collecting my full thoughts on this but I could see you leveraging a Hierarchy system module that defines each level of the hierarchy. Here is a great article that shows you how to setup a hierarchy system module.
What I would advise from here is come up with some logic that says in a 3rd line item, “If hierarchy level = Child 2, then Parent - Child1 else 0” then have a “final line item” that says, “If Parent then take parent line item, else if child 1 level then take child 1 line item else child 2 line item.”
Hope this helps. Let me know if you need help clarifying any step.
1 -
My current formulae are this:
LineItem1 = Total Staff
LineItem2 = IF CHILD1 THEN [CALC] ELSE 0
LineItem3 = IF CHILD1 THEN 0 ELSE LineItem1 - LineItem2
LineItem3 for CHILD2 it is doing LineItem1 - LineItem2 for CHILD2 (which is 0 based on the IF statement logic)
What I need I think is:
LineItem3 = IF CHILD1 THEN 0 ELSE LineItem1 - LineItem2(for CHILD1)
but I don't know how to get CHILD2 to retrieve the value from CHILD1
1 -
Hello @RickyTillson-
The hierarchy levels System module helps with this exact scenario. I am assuming your module is dimensionalized by Child 2.
What you would do is you need some conditionals that identify the level of hierarchy of Child 2, Child 1, and Parent.
Your formulas would be:
1) Line item 1= Total Staff
2) Line item 2= IF SYS Hiearchy Levels.L3 = 3 THEN Parent-Child 1 Else 0.
3) Line item 3= IF SYS Hierarchy Levels.L3>3 THEN (Child 1 formula) ELSE 0.
The summary methods on Line Items 2 and 3 will need to be formula. Then you can have a final line item which says IF SYS Hierarchy Levels.L3 THEN Line Item 2 ELSE IF SYS Hierarchy Levels.L3 = 2 THEN Line Item 3 ELSE Line Item 1.
@JaredDolich - tagging you here if you have any other suggestions or if you agree with the above.
1 -
I think @DaanishSoomar solution will work.
I think you can also leverage a system lookup module that has a list formatted line item with your child list. For that line item you will select the Child item you want to leverage for looking up (in this case you would choose the Child1 value).
Then in your Calculation module you can use that constant to lookup the value of a line item for Child1 regardless of the dimension you are in.1
