Child and Parent mixed level formula

Options
RickyTillson
RickyTillson Member, ALL USERS, Certified Model Builder Posts: 5 Occasional Contributor
in Planning & Modeling

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!

 

RickyTillson_0-1615893877165.png

 

 

 

 

Tagged:

Comments

  • MathisG
    MathisG Member, ALL USERS, GroupMember, Partner, Certified Model Builder Posts: 3 Occasional Contributor

    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 1Child 2Child 3
    Priority123
    Need605040
    Rankcumulate60110150
    allocation (50)5000
    allocation (120)605010

     

    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
    RickyTillson Member, ALL USERS, Certified Model Builder Posts: 5 Occasional Contributor

    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

Categories