Overall Health Symbols at Parent Level

Good Morning,
I am trying to add health symbols that will tell me the overall health of my project as well as the individual phases withing said project. Below is currently what I have at my children level for health symbol formula.

Children Formula for Health Symbol:

=IF(COUNT(CHILDREN(Tasks@row)) = 0, IF([Ticket_Status]@row = "Complete", "Green", IF([Ticket_Status]@row = "Pending", "Blue", IF(OR([Ticket_Status]@row = "Not Started", [End Date]@row < TODAY()), "Red", IF([End Date]@row <= TODAY(7), "Yellow", IF([Ticket_Status]@row = "In Progress", "Green", "Yellow"))))), IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Blue") > 0, "Blue", "Green"))))

I am trying to write formula or find best way to provide overall status per phase as well as overall status for the entire project. Any assistance would be greatly appreciated.
Basically where the red X's are located.

Find more posts tagged with

Formulas

Accepted answers

All comments

kowal

hi @RJT

have you heard about the Weakest Link method of showing it?

you need to add extra helping column to check if a main task or subtask so the main task will always take the weakest value of the subtasks.

If all the subtasks are green but one is yellow the main task will be yellow if one is red the main task will be red etc.

=IF(HasChildren@row <> 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Green"))), IF(Finish@row - 2 < TODAY(), "Red", IF(Finish@row - 7 < TODAY(), "Yellow", "Green")))

Kerry St. Thomas

You could use CHILDREN in combination with how you'd measure health of any phase or overall project. For example, =IF(COUNTIF(CHILDREN(Health@row),"Red")>1,"Red") and so on, depending on your specific calculation for health of phase & project in your specific use case.

One additional thing you can do is use what I call a Hierarchy helper column. It has column formula =COUNT(ANCESTORS(Primary@row)) in it. The formula returns a number - 0 is top level, 1, 2, 3, etc. are basically how many times a row has been indented.

If you use a column like this, you can layer your formulas you created, in order to make this one giant column formula - =IF(Hierarchy@row=2, [your big ol' formula above], IF(Hierarchy@row=1,[the phase health formula],IF(Hierarchy=0,[project formula],"") or some variation thereof.

Good luck!

=Chris Palmer

I have achieved this by doing the following.

1. Create a column titled "Parent/Child" and writing a formula that determines the Parent/Children:

=IF(COUNT(CHILDREN([Tasks]@row)) > 0, 1, 0)

Note: Anything that populates 1 is a Parent.

2. Formula determines parents with 1:

=IF([Parent/Child]@row = 1,
IF([Ticket_Status]@row = "In-Progress", "Yellow",
IF([Ticket_Status]@row = "Complete", "Green",
IF([Ticket_Status]@row = "Pending", "Blue", "0"))),"")

3. You can replace the "" with your formula for the children:

IF(COUNT(CHILDREN(Tasks@row)) = 0, IF([Ticket_Status]@row = "Complete", "Green", IF([Ticket_Status]@row = "Pending", "Blue", IF(OR([Ticket_Status]@row = "Not Started", [End Date]@row < TODAY()), "Red", IF([End Date]@row <= TODAY(7), "Yellow", IF([Ticket_Status]@row = "In Progress", "Green", "Yellow"))))), IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Blue") > 0, "Blue", "Green"))))

4. Final Formula:

=IF([Parent/Child]@row = 1,IF([Ticket_Status]@row = "In-Progress", "Yellow",IF([Ticket_Status]@row = "Complete", "Green",IF([Ticket_Status]@row = "Pending", "Blue", "0"))),
IF(COUNT(CHILDREN(Tasks@row)) = 0, IF([Ticket_Status]@row = "Complete", "Green", IF([Ticket_Status]@row = "Pending", "Blue", IF(OR([Ticket_Status]@row = "Not Started", [End Date]@row < TODAY()), "Red", IF([End Date]@row <= TODAY(7), "Yellow", IF([Ticket_Status]@row = "In Progress", "Green", "Yellow"))))), IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Blue") > 0, "Blue", "Green")))))

You can always simplify this for the children, but I'm using what you already created since it's there already.

Also with Step 1 you can use:
=COUNT(ANCESTORS(Tasks]@row))
This will capture grandchildren.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.