Calculating levels in a hierarchy for dashboard filtering

Summary

We explain here a dynamic way to filter specific levels of a hierarchy. This provides a better way to filter & visualize hierarchies. 

Overview

This tutorial explains how to calculate the level of a list in a hierarchy in order to apply specific calculations (custom summary) or filters by level.

In this example we have an organization hierarchy of 4 levels (Org L1 to Org L4). For each item in the hierarchy we want to calculate a filtering module value that returns the associated level.

Context and notes

  • This technique addresses a specific limitation within dashboards where a composite hierarchy's level cannot be selected if the list is synchronized to multiple module objects on the dashboard.
  • We show the technique of creating a static filtering module based on the levels of the composite structure.
  • The technique utilizes the Summary method Ratio of line items corresponding to the list levels to define the value of the filtering line items. Note that it is not a formula calculation but a use of the summary method Ratio applied to the composite hierarchy.

 

Example list

We defined in this example a 4-levels list as follows:

001.png

002.png

Defining the level of each list

In order to calculate the level of each item in the lists, we need to create a module that calculates it by:

  • Creating as many line items as level of hierarchy + one technical line item.
  • Changing the settings in the blueprint of those line items according to the following table:filter blueprint.png

Line Item

Formula

Applies to

Summary

Summary method Setting Ratio

Technical line item

1

(empty)

Formula

 

Level or L4 (lowest level)

4

Org L4

Ratio*

L3 / Technical

L3

3

Org L3

Ratio

L2 / Technical

L2

2

Org L2

Ratio

L1 / Technical

L1

1

Org L1

Ratio

L1 / Technical

 

 

 

 

 

         

 

When applying these settings, the calculation module looks like this:

*Note that the Technical line item Summary method is using Formula, Minimum Summary method can be used but will return an error when a level of the hierarchy does not have any children and the level calculated is blank.

004.png

 

 

We can now use the line item at the lowest level—“Level (or L4)” in the example—as the basis of filters or calculations.

 

Applying a filter on specific levels in case of synchronization

When synchronization is enabled, the option “Select levels to show” is not available. Instead, a filter based on the level calculated can be used to show only specific levels.


In the example, we apply a filter on the level 4 and 1:
005.png

 

This gives the following result:

006.png

 

 

The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.
Comments

Hi Mark,  

Can you provide another screeh shot showing what your are filtering in the dashboard please?

I was expecting a drop down of level number to filter on levels.  I have built the saved view - filtered the 'Turnover' module based on level numbers of the 'Hierarchy Levels' module but this is now hardcoded to Level 4 or Level 1.  If hardcoded then how am i filtering it in the dashboard?  Is your page selector simply enabling selections on the resulting filter?  Thanks

Labels (2)
Contributors