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.

Answers

  • Thanks for sharing this! Such a clear explanation! 

  • @rob_marshall 

     

    thanks for pointing out that there is flip side of this and remind us that we must keep an eye the performance always!

     

    About the alternative that you propose, I would appreciate a lot if you could elaborate: "using two dimensional modules and Boolean flags with ANY". I cannot get my head around it and it sounds very interesting. 

     

    Cheers!

     

    Alex. 

  • Hi @rob_marshall 

     

    thanks a lot for taking the time to provide further explanations. 

     

    I have been having a look on how could I use ANY, FirstNonBlank and the other functions as a replacement of TEXTLIST or as an "Inverse Lookup". 

     

    My conclusion is that, while x[ANY:y], x[ALL:y], x[MIN:y], FirstNonBlank and so forth, in some cases, can be used as a replacement for TEXTLIST - so is nice to know how they work and when can be used in order to improve the performance of our Anaplan Model - they cannot fully replace TEXTLIST

     

    Even when TEXTLIST is harmful in terms of performance and calculation speed when be abuse of this solution, there must be a reason for having this function among Anaplan's formulas in first place. So,In my eyes, while x[ANY:y] and x[ALL:y] are good options when handling booleans, and x[MIN:y], x[MAX:y], x[Average:y] and SUM are useful when it comes to number-formatted LI's, when it comes down to Text, FirstNonBlank and LastNonBlank might offer better performance by handling a lower amount of text, but do not offer the same result. 

     

    I will like to conclude with a simple example of this:

     

    If we have a list of Sales Reps with 2 attributes: Sales Region and Product Category

    Source.JPG

    And we would like to know the different product categories being sold per region, I would use Textlist:

    Target.JPG

     Since First and Last Non Blank will omit the in-between values. 

     

    The reason why I have written this reply is because it has been nice to me to stop for a while and compare the outcomes of the different formulas that you have proposed as alternatives. Then, I just wanted to share my conclusion with you @rob_marshall  in particular (since is always nice to read your eye-opening comments) and with anyone who might find this of any help.

     

    Cheers.

     

    Alex.

     

     

  • Thanks @rob_marshall  and @DavidSmith for the additional nuance and for sharing the PPT!  As usual, really useful stuff.

     

    Kind regards,

     

    Alex. 

     

     

     

  • Thanks for sharing @AlejandroGomez great explanation!

     

    Will definitely be sharing this! 

  • @AlejandroGomez ,

     

    Alex,

     

    Just replying at the bottom so the conversation goes in order, but I believe in your TextList() example, it can be modeled using ANY.  Take a look at the below and let me know if this satisfies your requirement.  Is it a different way of thinking, yes, but I think works for you.

     

    I have a mapping module as you did:

    2019-10-15_10-32-09.png

     

    I then have a module that is dimensionalized by both Product and Regions with the following formula:

    'Employee Mapping To Regions and Products'.Boolean[ANY: 'Employee Mapping To Regions and Products'.Regions, ANY: 'Employee Mapping To Regions and Products'.Products]

     

    Additionally, now you can use this module for different calculations (to see if Laptops were sold in a particular region) where as you can't using TextList().

     

    2019-10-15_10-28-58.png

    You could take it a step further and use the hardcoded 1 or Count line item and do a sum so you know exactly how many products were sold in a particular region with the following formula:

    'Employee Mapping To Regions and Products'.Count[SUM: 'Employee Mapping To Regions and Products'.Products, SUM: 'Employee Mapping To Regions and Products'.Regions] 

     

    2019-10-15_10-35-13.png

     

    Is it more space the using your TextList()?  Yes, but it is more performant this way due to all of the text joins textlist() is doing.

     

    Please let me know if I am missing something,

     

    Rob

  • 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. 

     

     

  • Great explanation! thanks for sharing

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