Returning custom derived values based on conditional IF THEN statements


I have a specific use case, but believe this is a useful item for myriad cases.

I have the below table of initiatives, grouped by components. I am pulling in the custom field “Project Status RYG” to show if those initiatives are Red, Yellow, or Green as updated on their JIRA boards. I would like to use a conditionally populated calculated member using the following pseudo code, based on the workflow status of the initiatives in JIRA:

  • IF “Project Status RYG” in (Red, Yellow, Green) AND Status != Closed THEN “Project Status RYG” = “Project Status RYG”
  • IF status = Closed THEN “Project Status RYG” = “Complete”

The idea here is that when a user moves the Initiative to closed in JIRA, it will reflect as completed in the R/Y/G view, rather than appearing to still be in progress, and without adding additional columns to the view.

Thanks so much, I think this sort of “change this, based on that” would be useful for all sorts of use cases when distilling information for executive leadership.