Lookup, Sum and Textlist Cheat Sheet

AlejandroGomez
Master Anaplanner/Community Boss

Lookup, Sum and Textlist Cheat Sheet

Hi all,

 

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. 

 

CHEAT SHEET

LOOKUP

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"). 

LOOKUP.JPG

 

 

TEXTLIST

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. 

TEXTLIST.JPG

SUM

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. 

SUM.JPG

 

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! 

 

Kind regards,

 

Alex.

12 REPLIES 12
AlejandroGomez
Master Anaplanner/Community Boss

Hi Rob,

 

I am glad you managed to put together a nice example with screenshots of using ANY as alternative. This example is really clear and meaningful.

 

Thanks!

 

Alex. 

 

 

Khanna
Contributor

Great explanation! thanks for sharing

Khanna
gvanhelsdingen
Occasional Contributor

@AlejandroGomez thank you so much for this explanation! Text list really came in handy today!