How to use “LOOKUP” with summary elements
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.
Comments
-
Especially useful article with a quite simple solution.
0 -
Good workaround. Thanks Anton!
0 -
Short videos are very helpful! Thanks Anton!
0 -
nice building trick ! Would you care to explain how you build the reporting part with fake users ?
0 -
This is a topic for a separate article, but I'll try to give a brief introduction.
In order for each user to see his sum (select any versions, accounts, and so on) and not to recalculate the sums of other users, we need a proper measurement.
Using Native Users can really bloat the model, because a particular report can be used by not all users of the model (extra sizing). Since Native Users has no subsets, we create a duplicate dimension and enable Selective Access for it, giving each user rights to "his" item. This way each user has his own version of the report.
0 -
Very useful workaround. Thanks for sharing @AntonMineev!
0 -
Thank you for sharing! Instead of using & creating a Line Item Subset for every new Target Reference, I would suggest to work with a Technical Item List in "Tech Map", which only includes one item an is added to the dimensionality of the taget module (no size increase, as all cells are multiplied by 1).
0
