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!

Roll_up_-_Text_Fields_127419.png

Accepted answers

All comments

JaredDolich

@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

rob_marshall

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

Rob

rohithnarayan

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.

rob_marshall

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

https://community.anaplan.com/t5/Anaplan-Platform/Tier-Pricing/td-p/70326

Rob

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…