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

AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…
Using time for something not time related - special characters :)$
We had a recent challenge with the need to "clean" invoice data descriptions from special characters. This was done previously in SQL with the a long convoluted formula "REPLACE(REPLACE(REPLACE('Ana&plan', '&', '') that would have to edited by a developer in case any new special characters came through. Using the replace…