Child and Parent mixed level formula

RickyTillson

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!

 

 

 

 

 

MathisG

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

RickyTillson

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