Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jchambers123
New Contributor II

Set expression with dynamic date range

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