How to create formula that calculates the first child of a given parent?

Highlighted
Certified Master Anaplanner

How to create formula that calculates the first child of a given parent?

Hi,

 

I need to map high level data (Product) to leaf level (SKU) for exporting planning information to transactional systems. 

 

Each product has multiple SKUs so we'd like to automate that mapping by using the first SKU within the hierarchy. I can calculate the SKUs sequence within each product using RANK function but I'm not sure how to use this information on a module where I only have the Product level.

 

In summary this is what I have at the moment:

Source Module: SKU Level

Source Line Item: I have a RANK showing that a given SKU is the 1st in that parent (or 2nd, 3rd etc...)

 

This is what I want to achieve

Target Module: Product Level

Target Line Item: First SKU of that Product

 

Can anyone please throw some light into it? Let me know if the info above is unclear

 

Regards,

Paolo

2 REPLIES 2
Highlighted
Certified Master Anaplanner

Re: How to create formula that calculates the first child of a given parent?

Solution found by a colleague:

 

1. Inside the target module on the target line item create a subsidiary view and add the SKU level

2. apply the following formula IF 'L4_SKU'.Rank = 1 THEN ITEM('L4_SKU') ELSE BLANK

3. apply for that line item the summary type "First Non Blank"

4. Each parent will then by default derive the value of the first leaf item

 

 

PS. The IF is not necessary for first children. The summary of the line item is what is actually doing the trick, but this if still might be useful if you want to get the 2nd or some other arbitrary member

 

Highlighted
Master Anaplanner/Community Boss

Re: How to create formula that calculates the first child of a given parent?

Can I just add it would be better to have that calculation in a System module (L4 Sku Details) which should hold all information about L4 SKUs.  This is the more logical place to house these calculations and avoid subsidiary views (which are ugly!!) and follows the DISCO module design methodology.  See below for more information

https://community.anaplan.com/t5/Knowledge/Best-Practices-for-Module-Design/ta-p/35993

 

David