In the following article, I would like to discuss a common case of model building: the LOOKUP function doesn’t work with summary elements of hierarchical lists. Among other things, this forces model builders to use SELECT and not configure Production Data for some lists. I want to present to you an alternative approach.
What is the problem?
For example, we have a simple module with P&L. We would like to show the user amount by account mapping:
Usually, model builders use the LOOKUP function:
Since these are aggregated values (e.g., Gross Profit) in a ragged list, lookups will not produce the proper values. The system will not generate an error but show 0.
What is the solution?
I would like to offer alternative method to help solve this problem:
Use the function IsAncetor() and a Line Item Subset (LIS)
- Make LIS with LI “Amount” (for further demonstration in the screenshots this is “Amount LIS SUM”)
- Make a new module for technical mapping
- We need to check, which list elements are the descendants of our mapping? Use ISANCESTOR formula.
- Create a new LI and turn the Boolean into the ITEM of our LI with Amount
Blueprint of the module after adding the mapping formula:
- Here we use formula SUM instead of LOOKUP, where the argument will be our LI in LIS format:
- Anaplan will understand the connection between the original LI and the LIS mapping. Thus, we summarize all the basic elements that are part of our mapping.
This approach works well with hierarchical directories and does not require much space. All you need is to create a technical LIS and write additional notes to it and the technical mappings.
Conclusions and considerations
I hope this approach will help you use “LOOKUP” with summary elements, and help you avoid using a SELECT formula for summation values and use ALM with the Production list assignment.
You can use this approach to avoid a SELECT formula when calculating various KPIs; for example, where you have to divide Gross Profit by Revenue. If you want to use the reporting approach (as in the example, with user selection) don't forget to use the Users dimension (I prefer Fake Users) so that everyone has the flexibility to build a report.
If you use this, let me know. It will give you more motivation to share your experiences.
