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