3 Replies Latest reply on Jun 12, 2017 6:02 AM by Michael Gillespie

# How do I calculate if consecutive months are equal to a particular value?

I am trying to calculate if the previous month and the month prior (although I may want to extend this to maybe 3 or 6 months prior at some stage) are greather than or equal to a particular value, in this secanrio whether any SLA breaches occured. I understand a calculated field is required but am struggling on finding the correct syntax in order to achieve this.

The syntax I currently have is as follows, and although this calculation is not valid - "Can't logically 'AND' integer and integer values" - but you will probably be able to see my thoughts towards approaching the syntax.

IF ((MONTH(MAX([Created Date])-1) AND MONTH(MAX([Created Date])-2)) AND [SLA Breach] >=1) THEN "True"

ELSE "False"

END

I would prefer to have a Boolean result, but again have been unable to find the correct syntax to achieve this.

I am currently using Tableau Desktop vesion 10.1

• ###### 1. Re: How do I calculate if consecutive months are equal to a particular value?

Steffan, it's tricky to debug calculated fields without data but on a first glance I think your major issue is that this statement:

IF ((MONTH(MAX([Created Date])-1) AND MONTH(MAX([Created Date])-2))

cannot be true ever.  A date cannot be both <date-1> and <date-2> at the same time.

IF MONTH(MAX([Created Date])-1 AND [SLA Breach] >=1) THEN "True"

ELSEIF MONTH(MAX([Created Date])-2 AND [SLA Breach] >=1) THEN "True"

ELSE "False"

Or maybe:

IF(MONTH(MAX([Created Date])-1 AND [SLA Breach] >=1))

OR IF(MONTH(MAX([Created Date])-2 AND [SLA Breach] >=1))

THEN "True"

ELSE "False"

• ###### 2. Re: How do I calculate if consecutive months are equal to a particular value?

Hi Michael,

Thanks for your assistance and advice regarding my previous formula, though the suggested formula's you provided didn't solve the problem I had.

I did some further research and found that the formula below actually gave me what I was looking for.

IF Lookup([SLA Breach],LAST()) >=1 AND

Lookup([SLA Breach],LAST()-1) >=1

THEN "True"

ELSE "False"

END

• ###### 3. Re: How do I calculate if consecutive months are equal to a particular value?

Excellent!  Glad you found a solution.