1 Reply Latest reply on Mar 7, 2018 2:13 PM by Eric Hammond

# show records in which threshold was breached in consecutive months

Hi,

I am new in using Tableau so trying to figure out how to filter records based on a parameter. So I want to show all the records where values have reached a certain threshold. For example, if I have records for 1 year and show values by month, say values are from 1 to 100, and the threshold is say 70. I want to show the records that have reached 70 or greater in 3 consecutive months.

I have created a parameter with the list containing values such as 2,3,4,5. If I select 2, it will show me records in which threshold have been breached for 2 months or if I select 5, it will show me records that breached the threshold 5 times etc. Hope someone can guide me in achieving this.

• ###### 1. Re: show records in which threshold was breached in consecutive months

Hi Jessa,

A calculated field can reference your parameters for [Consecutive Periods] and [Threshold], and then the calculated field can be used as a filter (or for color, size, etc.).  This calculation was verified using Superstore, looking for consecutive months that have sales exceeding the value in a [Threshold] parameter.

IF SUM([Sales]) < [Threshold] THEN "N"

ELSEIF LOOKUP(SUM([Sales]),-1) < [Threshold] THEN "N"

ELSEIF [Consecutive Periods] < 3 THEN "Y"

ELSEIF LOOKUP(SUM([Sales]),-2) < [Threshold] THEN "N"

ELSEIF [Consecutive Periods] < 4 THEN "Y"

ELSEIF LOOKUP(SUM([Sales]),-3) < [Threshold] THEN "N"

ELSEIF [Consecutive Periods] < 5 THEN "Y"

ELSEIF LOOKUP(SUM([Sales]),-4) < [Threshold] THEN "N"

ELSE "Y" END

If you allow more than five consecutive periods in your parameter then the formula would need to be adjusted to fit.