Authors: Upali Wickramasinghe, Certified Master Anaplanner and Principal Solution Architect at Zooss Consulting in Australia; co-authored by Andrew Petersen, Senior Consultant at Deloitte Australia.
We were presented with a use-case to utilize the Anaplan Hierarchy Charts to display an organizational structure with some key elements displayed in the nodes such as Employee Name, Country, and Department. The nature of this organizational structure is an unbalanced or “ragged hierarchy”, which made the displaying of values tricky because the need to use for Formula Summary Method for the Line Items used. We present a method using TIMESUM that provides a scalable solution for any ragged hierarchy use-case that requires to utilise the Formula Summary Method.
Note: This approach only works with the Anaplan Classic engine and was developed before the Hierarchy Card allowed for Data Lists. However, the methodology we present can be used for any ragged hierarchy use case even if the application is not for org charts.
Approach we took
Consider the following List that has been defined as a single ragged hierarchy. The use of a Numbered List is not possible if the need for a ragged hierarchy is required instead of a Composite Hierarchy.
An associated Properties (PROP) module is defined as:
This information can now be easily defined using a Hierarchy Chart in the Anaplan UX as:
As one can see, the line item with Formula Summary method can be displayed accurately on the UX.
Now, consider the additional properties of the Employees to be defined in a simple flat PROP module as:
We would like to LOOKUP some of these Line Items in the same module used to display the values in the UX. A simple LOOKUP can achieve this as:
However, note that we did not set a Summary Method, because no suitable summary method can be applied when a LOOKUP formula is used. Last Non-Blank or First Non-Blank do not yield accurate information.
Therefore, the output looks like:
When presented in the UX, the information looks like:
This is not the ideal output we want because all elements that are Parents will not show the information needed. Only the leaf elements are able to show the information defined in the module accurately.
We used the following workaround to achieve the desired outcome of being able to set an accurate Formula Summary method.
Step 1: Define a Numbered List to hold the same Employee codes defined earlier. We use a numbered list to take advantage of the System codes (i.e. defined by “#”) that are in a sequential order.
Step 2: Create a corresponding PROP module to link back to the Employee data.
Step 3: Map a date to each of these elements.
Here we utilized a simple SYS module to hold a “Starting Date” as:
Then “Offset” the Numbered Lists System code to derive a corresponding date value as:
Step 4: Define a SYS module that can now associate an Employee to the corresponding Date values. Here we utilize the FIRSTNONBLANK aggregation method as:
Now that we have managed to “lookup” the Employee, the associated other attributes required to be presented can be easily derived.
Step 5: Let’s now update the PROP module used for the UX with the following additional Line Items.
The key here is to associate the Employee to the defined Numbered List and then “lookup” the Date that was mapped to that element in the numbered list. Once we have the associated Date, then we can use TIMESUM, which is applicable on Formula Summary Method to elegantly “lookup” the associated attributes needed.
The end result is the module being able to display all the accurate details at each element of the ragged hierarchy as:
Displayed on the UX is:
This approach we present is scalable to any ragged hierarchy and will also function even when elements of the hierarchy is deleted or updated as the SYS module used to map the Employee details with a Date value will simply be BLANK if an element is not in the original hierarchy.
Note that this approach only works in the Classic Anaplan engine, because in Polaris the LASTNONBLANK or FIRSTNONBLANK aggregation methods are unavailable and the TIMESUM function can only be used on Numeric and Boolean values.
Conclusion
This article presented a scalable solution to set a Formula Summary Method on a ragged hierarchy to accurately display values used in the display of an organizational structure in the Anaplan UX via Hierarchy Charts. Although this use case was to present information in a Hierarchy Chart, the approach can be used for any other use cases that require the use of a ragged hierarchy and line items that are not Numeric or Booleans.
Questions? Leave a comment!
