2 Replies Latest reply on Nov 19, 2013 11:42 AM by Julie Sauvageau

# How to calculate and report on "consecutive" months for a specific status?

We have a simple list of projects status results:

Project A, Month1, Status (Red)

Project A, Month2, Status (Green)

Project A, Month3, Status (Green)

Project A, Month4, Status (Red)

Project A, Month5, Status (Red)

Project B, Month1, Status (Green)

Project B, Month2, Status (Green)

Project B, Month3, Status (Red)

Project B, Month4, Status (Red)

Project B, Month5, Status (Red)

Project C, Month3, Status (Green)

Project C, Month4, Status (Green)

Project C, Month5, Status (Green)

I want to display a graphic showing the average "Consecutive Problematic Months" for projects currently in problem only.

For Project A: 2 (the first "Red" status is not consecutive)

For Poject B: 3

Project C should be out of scope (not counted)

I tried to play with formula but I failed...

I'm thinking that maybe the solution is a calculated field in Excel (macro) to execute before loading in Tableau; but I'm trying this forum before! Thanks!

• ###### 1. Re: How to calculate and report on "consecutive" months for a specific status?

Hi Julie,

This can be done by creating a calculated field for Status that essentially assigns a number to each (Red or Green). Similar to the following:

if Status='Red' then 1 else 0 end

Then, create a second calculation similar to the following:

if sum([Status Number]) = 1 and sum([Status Number]) =lookup(sum([Status Number]), -1) or sum([Status Number]) = 1 and sum([Status Number]) =lookup(sum([Status Number]), 1)then 1 end

You can then, filter out the null values and you have all your consecutive Red statuses.

Hope this helps!

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: How to calculate and report on "consecutive" months for a specific status?

Hi,

that was helpful - and we already a similar calculation for counting ALL red status.

But I'm still struggling to keep only the last problematic sequence - in my example, for the Project A, I need only the last sequence.

If a project has 3 red results, then some green, then 4 red results again - how can I display only 4 (and not 7...)

Also - when I'm filtering, numbers are changing (I think) - is there a way to "fix" a value or to make a calculate field ignoring filters / grouping?