-
1. Re: show records in which threshold was breached in consecutive months
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.