The Bedford Coach - Identifying Hierarchy Levels
In this month’s instalment of the Bedford Coach we will be covering the best practice method to identify the level of an item in a multiple list hierarchy.
Anaplan definition: List Hierarchy
Lists can be aggregated into a structured hierarchical list. There are two kinds of hierarchical lists: composite lists and parallel hierarchies.
Composite lists are also referred to as composite hierarchies or hierarchy lists.
Composite lists are created from a series of lists, each of which is assigned a parent in the hierarchy. Complex structures can be defined very rapidly. The component lists in a composite hierarchy can be used many times within a model, in many different composite lists, reducing cell count and sparsity for the model.
A parallel hierarchy is a hierarchy that shows a set of data organised in two completely different ways.
For example, one hierarchy can display the data for cost centres in an organisation by functional area: machines division, consulting division, head office. The same data for those cost centres could also be displayed in a different hierarchy by area: North, South, East, West. These two hierarchies comprise a parallel hierarchy – the same data presented in different ways.
What we want to achieve:
For any multiple list hierarchy we want to use the calculation logic in Anaplan, using a combination of lists and a module to inform us at what level in a hierarchy a list item sits.
So in the example below using a customer/product structure in a multiple list hierarchy, we can calculate the zero-up or zero-down level of each item in the list – one being the reverse of the other.
Case study: Calculating levels in a multiple list hierarchy
Create a module called “Hierarchy Calculation multiple list hierarchy” adding to it the list at the level from which you want to calculate your levels from. In this instance we are using the L.5 Customer - Brand Variety Unit list (screenshot), the lowest level in the multiple list hierarchy.
You can see structurally that the L.5 Customer - Brand Variety Unit list is one of five lists in a five level composite hierarchy, in that it is comprised of 5 separate component lists; L.4 is the parent of L.5 (lowest level), L.3 is the parent of L.4 and so on. If you were to open this list in Grid view it would look something like the following
We then add the following line items before creating the module….
Level - Zero down
Level - Zero up
*Note if your hierarchy had more or less component lists you would need to adjust the number of line items in the module to account for that.
You will always need the Technical, Level - Zero down and Level - Zero up line items, but would adjust the amount of L. line items depending on the number of component lists. If you had a 7 level hierarchy you would have L.1 to L.6 followed by Level - Zero down.
Once the module has been created you need to adjust the Applies To for line items L.1 to L.4 so that they apply to the lists they represent in the multiple list hierarchy e.g. L.2 line item will have an applies to of the list L.2 Customer – Brand and nothing else. It should look like this in the module blueprint. Note there is no time scale or versions applied to the module.
Once the module is in place we can now add the formulas, which are very simple.
Technical = 1
L.1 = 1
L.2 = 2
L.3 = 3
L.4 = 4
Level - Zero down = 5
Level - Zero up = 5 - 'Level - Zero down'
*Note - Like motioned above, if we were using a 7 level hierarchy Level - Zero down would = 7, not 5.
This step involves changing the summary treatment for all of the line items, this being the clever part that works out the levels for us and ensures they are displayed correctly.
Simply assign the following summary methods for each line item
Level – Zero down
Level – Zero up
Once you come out of blueprint mode the module should look like the following screenshot. There are now two level identifying line items that are calculating the correct levels of each list item in the hierarchy.
You can add new items to the ANY of the hierarchies and it will continue to work without any need to makes changes to the module.
Typical Use Cases
Filtering a dashboard to show/hide different levels in the hierarchy when synchronisation is enabled. This is a useful way to get around not being able to use Select Levels to Show when synchronisation is enabled on the list.
The filter applied as below would filter the dashboard view to only show level 0 to level 3 list items.
Calculations that are specific to a hierarchy level – in the example below a formula has been written to show the text for list items that are level 0 to 3.
Stay tuned for the next edition of the Bedford Coach.