Get Unique Values Using Source[TEXTLIST: Mapping]
"Textlist" is one of my favorite formulas when trying to reduce sparsity in calculations. However, Source[TEXTLIST: Mapping] becomes ineffective because there isn't the option to rollup unique values. For anyone who ran into the same challenge before, hope this post can be helpful.
Workaround:
- Use rank function to stage the data set before using “Source[TEXTLIST: Mapping]”
Example Below:
Data Set: The list of support tickets by opportunities. Each support ticket was assigned to a person.
End Goal: Calculate the connected revenue for each employee who worked on the support ticket for the opportunities. Revenue amount is available at opportunity level.
Steps: To get to the end goal without bringing in extra dimensions, I used the textlist formula to show the people who worked on the opportunity. The issue with directly using the “Assigned to EEID” field is that there are duplicated values. I don’t want to credit the same person multiple times for the same opportunity, so I need to stage the data to show just the unique value.
Step 1) - turn the text field into a numeric field. Assigned to EEID_Number = Value(Assigned to EEID)
Step 2) - using rank formula Assigned to EEID_Rank Seq = TEXT(RANK(Assigned to EEID_Number, ASCENDING, SEQUENTIAL, Assigned To EEID <> BLANK, Oppty_List)). Text() formula is optional, just my personal preference.
Step 3) - using another rank formula Assigned to EEID_Rank Min =TEXT(RANK(Assigned to EEID_Number, ASCENDING, MINIMUM, Assigned To EEID <> BLANK, Oppty_List)). Again Text() formula is optional, just my personal preference.
Step 4) - Leave only unique values Assigned to EEID_Unique = IF Assigned to EEID_Rank Seq = Assigned to EEID_Rank Min THEN Assigned To EEID ELSE BLANK
After completing these 4 steps, I was able to use Assigned to EEID_Unique to show the unique employees who worked on the opportunity using Assigned To EEID_Unique[TEXTLIST: Oppty_List] and credit each of them once with the opportunity amount.
Comments
-
First of all congratulations on being Master Anaplanner of the Year:)Please do not use TEXTLIST at all. It is the worst performing function out there and it breaches Planual Rule 2.02-11. As my mentor @rob_marshall says it is brutal.
In your case I think you can use
Assigned To EEID_Unique[FIRSTNONBLANK: Oppty_List] or
Assigned To EEID_Unique[LASTNONBLANK: Oppty_List]
Let me know your thoughts on this
Misbah
Edit: Also if you wanted to see only Unique Values ISFIRSTOCCURRENCE would have done the trick
0 -
TEXTLIST is heavy but sometimes un-avoidable, as stated in the planual.
I've never thought of this function to reduce sparsity though.
0 -
I concur. Sometimes it is unavoidable to proceed without TEXTLIST but in this case it is avoidable.
0 -
@Misbah Thanks for taking the time to read through the post! I am aware that Textlist is not an ideal function to use, however, FIRSTNONBLANK AND LASTNONBLANK is not going to work for this problem. If for every opportunity there is only one person assigned, then there is no issue with your solution. However, it is guaranteed that an opportunity will be touched by more than one person. I need the list of all the individuals who worked on it and get credit for the effort. FIRSTNONBLANK AND LASTNONBLANK will only pick the first person or the last and ignore everyone else. Same goes for ISFIRSTOCCURRENCE. Hope this helps clarify.
Textlist is one of the oldest formulas out there and it is really unavoidable as @nathan_rudman mentioned in these types of scenarios, I do think it is a good comprise to make to avoid sparsity knowing it might slow down model performance a bit and also has the10,000 characters limit.
1 -
Serena - would IsFirstOccurrence(), in your instance, work if you concatenated the opportunity with the employee ID? Using TextList() is, as @Misbah stated, absolutely brutal on performance and memory consumption. And yes, textlist() is one of the oldest functions we have, but might in fact be the worst, from a performance standpoint.
Rob
0 -
To elaborate on the sparsity benefit I mentioned. Here is how I typically go about it.
In places where I see one to many relationships, like the one below where one opportunity can be associated with a handful of people. I use textlist to aggregate up the unique values ("Specialist Sales Assistance" column) and then parse out each person. In the example below, there will be 7 columns, each column has a unique ID. Then I use the parsed data for calculation instead of nesting the people dimension with thousands of records.
Oppty ID Specialized Sales Assistance 0060b00000s6vWSAAY 102869, 104301, 102653, 102418, 105541, 101860, 106476 0 -
Hi Rob - good to see you here!
I can see how IsFirstOccurrence() can replace the rank() formulas to get to the unique value, but I don't think it can replace textlist.
0 -
Agree on not replacing the textlist(), but it could be an avenue in replacing textlist() by using ANY as the ANY needs to have a boolean.
Rob
0 -
@rob_marshall I have done the ANY approach too, but will that cause unnecessary data sparsity though, if combining the 2 dimensions will create a module that doubles the size of a model?
0 -
Again, all instances are different, but sparsity is not the enemy like it was when we both first started doing this. By having your 7 different members being amended to the result, and then piecing that back apart, the overall memory used by the core is crazy. Essentially, it is doing a concatenation every time for every cell. And doing the concatenation kills memory. For instance, if your model is 80 GB (what you see when you log in), that model very well may be taking up to 150 GB in memory by having text concatenations throughout the model.
Instead of doing the textlist(), I was thinking of doing a count to see how many people are really associated to that opportunity and then summing that (at the opportunity level). Then, if you need to know who needs to get paid for it, you could have a boolean (the isfirstoccurrence piece, possibly), but you would need to dimensionalize they employee by opportunity which could be huge and an issue. I see what you are saying and what you are trying to do, but using Textlist() should be the last resort.
Rob
0 -
Thanks @rob_marshall. At the end of the day, I do see Textlist being a very useful and convenient function and I do hope (if it is possible) Anaplan can make it more efficient some day.
0 -
Ah I see what you saying. You are the better judge here in business requirement:)
As long as we understand the downsides of using TEXTLIST() we are good. I know End users rarely inquire about the the model size instead they will keep asking & checking model performance. TEXTLIST impacts the performance but if you are willing to take the risk it's perfectly alright. I am sure you have PLAN B in mind if you need to get away with this approach.
Happy Modelling
0
