Hello Community,
I have a chart that displays data at the quarterly level, and is dynamic such that the columns present the totals (e.g., of sales) for the quarter period selected by the user, or 1 quarter prior to the selection, 2 quarters prior, etc. To illustrate, here is the set expression I used for displaying 1 quarter prior to the user's selection:
=sum({<date=,YearQuarter,date={">=$(=QuarterStart(AddMonths(Max(date),-3)))<=$(=AddMonths(Max(date),-3))"}>} Responses)
The set expressions work properly. However, I need to create an expression that is even more dynamic by expanding (or not) the date range included depending on whether or not the totals are sufficiently large. Here's an example: I want the expression to display the sales for a particular quarter period only if there are 100 or more total sales in that quarter. If there are less than 100 sales in the quarter, I want the expression to aggregate THAT quarter plus the PREVIOUS quarter's sale and display that (combined) total for both of those quarters. And if current and previous quarter's sales are still below 100, then I want the expression to aggregate current, prior, and 2 prior quarters, etc. until the combined total equals the criterion of 100+ (This is what I meant by "expanding the date range" of the expression).
Here's some sample data:
Year/Quarter Sales
2017 Q3 110
2017 Q2 70
2017 Q1 30
2016 Q4 10
2016 Q3 80
2016 Q2 40
2016 Q1 10
2015 Q4 15
2015 Q3 20
2015 Q2 15
Using a criterion of "calculate only if total >=100", the expression I need would display the following (based on the user's Year/Quarter selections). If the user selected...
2017 Q3 ---> 110 would display (because the total 110 for that quarter is greater than criterion of 100)
2017 Q2 ----> 100 would display (the total 70 for that quarter is below criterion, therefore the expression would combine 2017
Q2 + 2017 Q1 = 70 + 30 = 100, to reach the criterion)
2017 Q1 ----> 120 would display (the total 30 for that quarter is below criterion, therefore the expression would combine it, and
the previous TWO quarters, 30 + 10 + 80 = 120, to reach the criterion)
2016 Q4 ----> 130 would display (the total 10 for that quarter is below criterion, therefore the expression would combine it and
TWO previous Quarters, 10 + 80 + 40 = 130, to reach the criterion)
2016 Q3 ----> 120 would display (....combines that quarter and prior one, 80 + 40 = 120 to reach criterion)
2016 Q2 ----> 100 would display (....combines that quarter and prior THREE quarters, 40 + 10 + 15 + 20 + 15 = 100 to reach criterion)
So, as I hope I've explained, what I need is a set expression that first evaluates the total for the selected quarter period, and summate if meets the rule/criterion (above a certain number), and if it doesn't meet the rule, increases the prior date range until the total (across those periods) does meet the rule. My guess is that I need some kind of variable or counter which would go in the offset parameter of the below set expression (in the location where I've inserted "????"), but I've searched the forum and what I've found hasn't been exactly relevant to what I'm looking for.
=sum({<date=,YearQuarter,date={">=$(=QuarterStart(AddMonths(Max(date),?????)))<=$(=AddMonths(Max(date),0))"}>} Responses)
Any input or suggestions you can provide would be greatly appreciated. Thank you in advance.
-John
