Ignore Parent Records formula & Ranked graph
Hello. I am trying to create a graph that shows the top 10 tasks ranked by the variance between Initial Duration and Actual Duration. To make this work, I want to ignore tasks that are parent records. What would this formula look like? I have created a formula to rank the Variance; =RANKEQ(Variance1, Variance:Variance, 1). I also added a column with formula to show the level of parent/child it is. On the pic below, the second column is the Variance (in months), the third is the "Levels" column, and the fourth is the "Variance Rank" column.
As I've looked at a solution, the issue I run into is how to treat each task individually. I want "Site Utilities" and "Footings for Core" to be considered in the ranking even though they are of different levels in the parent/child levels.
All help is appreciated. Thanks.
Comments
-
If you change your Levels column to something like...
=COUNT(CHILDREN())
It will return a zero for any rows that are not a parent row regardless of what level of hierarchy they are on.
.
Then you could use a JOIN/COLLECT/LARGE combination to pull together all rows that are ranked as the first highest, second highest, etc. along the lines of
=JOIN(COLLECT(Task:Task, Levels:Levels, @cell = 0, [Variance Rank]:[Variance Rank], @cell = LARGE([Variance Rank]:[Variance Rank], 1)), ", ")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!