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").
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.
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.
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!
2 -
Great explanation, but please do no use TEXTLIST() as this is a huge performance impact and goes directly against Planual Rule 2.02-11:
TEXTLIST requires a lot of memory for calculations and should be avoided if possible, using two dimensional modules and Boolean flags with ANY is a good alternative.
Thanks,
Rob
6 -
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.
2 -
You can use the ANY in the same way was SUM or Lookup, as well as FirstNonBlank and LastNonBlank. Honestly, I didn't know we could do this until David showed me:
ANY:
FirstNonBlank:
https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/FIRSTNONBLANK.html
LastNonBlank:
https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/LASTNONBLANK.html
In each of those, the line item can be Text formatted and are way better than using TextList.
6 -
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
And we would like to know the different product categories being sold per region, I would use Textlist:
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.
2 -
thanks for this
Understanding SUM and LOOKUP are the key to transformations in Anaplan, thus the foundational knowledge you need to build good models.
We have focussed on this extensively in the new L1 training, with a specific lesson on it. I have also attached a presentation I put together for CPX to help explain the differences and the techniques I use to get the mapping correct
With regard to TEXTLIST, the primary function is to produce a list of all of the difference text values. So all the regions covered by a sales channel. However, as @rob_marshall mentioned, it is a very poor performing calculation.
It is especially poor when used in conjunction with other functions such as FIND (e.g. territory assignments). That is where we need to model differently and as mentioned, using two dimensional module with booleans is much more efficient. As with other functions, and constructs, there may be times when you have to use them, but "use with caution" and don't use them if there is an alternative
I hope this helps clarify things
David
5 -
Thanks @rob_marshall and @DavidSmith for the additional nuance and for sharing the PPT! As usual, really useful stuff.
Kind regards,
Alex.
0 -
1
-
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:
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().
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]
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
4 -
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.
0 -
Great explanation! thanks for sharing
1 -
@AlejandroGomez thank you so much for this explanation! Text list really came in handy today!
1
