Anaplan Roll up for Text Fields
Hello,
Requirement :- The parent should have the average color of its children.
I have an Indicator(Red , Yellow, Green) coming as a Text input from the Source.
The requirement is to roll up to its immediate parent and its final parent subsequently based on the average color as per the children hierarchy.
Kindly let me know how to we aggregate / average text values.
Thank you very much in Advance!
Answers
-
Definitely give RANK and/or RANKCUMULATE a try.
- Assign a value of one to every child member.
- Count them up by color within the parent
- Rank them
- Create a line item that identifies the number 1 rank and you've got your "average" color
1 -
Something like this? Try to stay away from Text, my indicators are list members and I am using code (1- Red, 2- Yellow, 3 - Green) to get my numbers.
Rob
1 -
Thank you @rob_marshall and @JaredDolich for the quick inputs. Based on inputs, I have the following
Since the Source data is coming as Text, I cannot avoid Text format.
I did assign values to each color and did have a formula Summary for a new field which would be exposed in dashboard with the formula below.
Value Assigned:-
IF Text Input = "Green" THEN 1 ELSE
IF Text Input = "Yellow" THEN 2 ELSE
IF Text Input = "Red" THEN 3 ELSE 0
Final Color - Export
IF Value Assigned >= 0.5 AND Value Assigned < 1.5 THEN "Green" ELSE
IF Value Assigned >= 1.5 AND Value Assigned < 2.5 THEN "Yellow" ELSE
IF Value Assigned > 2.5 AND Value Assigned < 3.5 THEN "Red" ELSE "Black"
Kindly confirm if this logic holds good with huge data.set.
Conditional formatting might not 100% match to its value, but I think its the nearest what we can achieve.
0 -
Text is not good to use....If the source system is text, you can create a "regular" list that when the text is imported, it will identify the list member. Again, please refrain from using Text. And I wouldn't create the nested IF THEN ELSE statement, but rather follow this article, even though the author is a bit sketchy (lol).
https://community.anaplan.com/t5/Anaplan-Platform/Tier-Pricing/td-p/70326
Rob
1
