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!

Accepted answers

All comments

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

DaanishSoomar

@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.

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

DaanishSoomar

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.

matthew.mccurdy

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.

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…