Anaplan Roll up for Text Fields




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!





  • @rohithnarayan 

    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
  • @rohithnarayan 


    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.







  • Roll up - Text Fields - 1.png


    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.

  • @rohithnarayan 


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