Lookup, Sum and Textlist Cheat Sheet
During the last CPX in London, which was fun and insightful, came to my attention the fact that still now-a-days the most searched topic in Anaplan Community is "how to use Lookup and Sum".
That is why I decided to share with you all this visual and brief explanation of how Lookup, Sum and Textlist work, I hope you find it as handy as I do!
This post is intended to be complementary to the existing explanations in Anapedia, a "cheat sheet" to be revisited quickly when you get doubts about how these formulas work or you are not sure which one to use to make the data flow into your module.
To use this Cheat Sheet you only need to bear these 3 points in mind:
1. Think in terms of Source Module (where our data currently is) and Target Module (when do you want to retrieve it)
2. Be aware of the dimensionality of both modules (Source and Target)
3. Be aware of the format of the Line Items being used. Either because some functions only work with data in a specific format (SUM with Numbers and TEXTLIST with Text) or because you will need LI formatted as the Lists being used as Dimensions in Source/Target modules.
Target Module must have a LI formatted as a List ("LI: Employee" in the example below) being used as a Dimension in Source Module ("Dim Employee").
Even though this formula is meant to retrieve text strings, it can be used as a Reverse Lookup. The only limitations is that, whereas Lookup can retrieve any kind of data formats, Textlist only retrieves Text-formatted values.
So turn your source data into text, get them into your Target Module and convert them back to their original format.
It works quite similarly to TEXTLIST but only works with Number-formatted Line Items. For instance, in the example above, since Salary is by definition a Number-formatted magnitude, we should have used SUM.
Remember that SUM is to be used when there is no hierarchical relation between the Dimensions in Source and Target Modules.
- If one of the Dimensions in Target is Parent of the Dimension in Source, aggregation happens automatically (as long as Summary Method is not set to "None" in the Source Line Item)
- If one of the Dimensions in Target is Descendant of the Dimension in Source, then LOOKUP should be used.
I hope this helps you to connect your data across modules rapidly next time you hesitate about how to do it!