Sort a flat list with a composite hierarchy

serena.zhang

For those of you who have touched HC or resources planning might be able to relate to the example below. Composite hierarchy is very useful, however, in some cases it is significantly easier for end users to managing data with a flat list. In those scenarios, sorting using a hierarchy can be helpful.

 

Example: 

When headcount is managed based on reporting relationships, not all organizations have the same number of management layers. The ragged hierarchy can make Anaplan user interface very cumbersome and reduce adoption. Therefore, some users would prefer to manage headcount using a flat list, but sorted based on the reporting relationships. Below are the steps to create the customized sorting.

1. Start from a flat list of all HC

2. Create the composite hierarchy using reporting relationships (“Manager Column” from screenshot above)

3. Build the sorting table as shown below. (Each record on the composite hierarchy needs to have a code. If the code is not numeric, you can leverage numbered list and the anaplan generated “# xxx” for the calculation.

• Section “List Code” - converts codes to numeric values
• Section “Ranking” - ranks the records on the list using values from section 1.
• Section “Sorting Code” - generates sorting codes for records on each level of the hierarchy. 
• “Sorting Code (1-3)” is the final output. Formula: 'L1_Sort'[LOOKUP: 'HC L1'] & 'L2_Sort'[LOOKUP: 'HC L2'] & 'L3_Sort'[LOOKUP: 'HC L3']

4. End result when you sort on the “Sorting Code (1-3). You can use color coding on the levels or a headcount attribute to differentiate roles and levels.