Sort a flat list with a composite hierarchy

serena.zhang
serena.zhang
in Modeling

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

SerenaZ_0-1591741299111.png

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

SerenaZ_1-1591741299372.png

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']

SerenaZ_2-1591741299285.png

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.

SerenaZ_3-1591741299115.png

 

 

Tagged:

Answers

  • rob_marshall
    rob_marshall

    @serena.zhang ,

     

    Great post.  A couple suggestions which will make this easier to maintain as well as perform better:

    • instead of having the subsidiary views, put those line items in their own module so others can benefit from this logic and not have to hunt around for it.
    • On the concatenations with the delimiters, go ahead and do the concatenation in the H1 and H2 modules because they have less members to do the concatenation.  For example, for L1, create another line item "L1 Sort with Delimiter" and have the concatenation in there.  Then, you can just reference this when creating the code of L2.
    • For the L2 sorting code, you can create two line items:
      • One for just the L2 code which would reference the line item from above that already has the concatenation (L1 Sort with Delimiter&L2 Code)
      • Create another line item which will be used for L3 that already has the concatenation done (L2 Code &"_")

    Again, just trying to limit the number of concatenations done and putting this logic in the proper modules so all model builders will know where to find this logic.

     

    Rob

  • DavidSmith
    DavidSmith

    Just to say, if you haven't already found it we have released an action to automate the sorting on a list

    Details can be found here

    https://help.anaplan.com/anapedia/Content/Modeling/Build%20Models/Actions/Order-list.html

     

    Hope this helps

    David

Categories