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

    show records in which threshold was breached in consecutive months

    Jessa Aballe
    Jessa Aballe Mar 6, 2018 12:01 AM

      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
          Eric Hammond
          Eric Hammond Mar 7, 2018 2:13 PM (in response to Jessa Aballe)

          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.