Rolling period FILTER PANE

[Jorich919]

I have a dashboard with 5 revenue KPI's and I have added a filter pane for YEAR and one for QUARTER as well. However, my manager has asked to also have a filter pane to be able to choose 3, 6 or 12 month rolling periods that change the results of all 5 KPI's. My date data is of the format 7/1/2019, 6/1/2019, 5/1/2019, etc. dating back to beginning of 2016. 

I have seen code to create a rolling period KPI of the format:

sum({<[Date]={">=$(=MonthStart(AddMonths(Max(Today()),-12)))<$(=MonthEnd(Max(Today())))"}>} Revenue)

I have put filter pane in CAPS in my subject line because I do NOT want my KPI to be a rolling period KPI.  I want the KPI's to be default for all dates, but then be able to filter to those rolling months options.

Is there a way to use 3 different versions of that set expression in a filter pane the way I wish? 

What is best way (and easiest way for someone fairly new to Qlik)? 

BTW....I have tried a Variable Input extension and replaced the -12 with the variable, and then offered 3 variables (3,6,12) but there is no way to turn that off so as to default to all months.

Thank you!!

[sunny_talwar]

There are few different ways you can do this...

1) When nothing is selected in the variable, you can default to a very large number... like -1000... this should show you all the dates

2) You can have a totally different expression when you have not selected a value in the variable.

3) There might be other ways, but one of the two ways above should be able to handle it for you. If not, please share a sample where we can see the issue and recommend a more precise answer

[Jorich919]

Thank you Sunny_talwar for the reply!
Option 1 works very well, except for one problem. My apologies for not mentioning this up front, but I also have a line chart on the same sheet that needs to change also. The Var Input option only changes the KPI's.
Can that line of code with the variable (replacing the -12) also be used in a visualization to limit the dates there as well?

[sunny_talwar]

Are you using the same expressions at the both places? If you are, then I don't see why it would not behave the same way?

[Jorich919]

I am but I am getting an error. Possibly my syntax. How do I use this KPI expression....

sum({<[Date]={">=$(=MonthStart(AddMonths(Max(Today()),-vRoll)))<$(=MonthEnd(Max(Today())))"}> } Revenue)

...to then replace the chart dimension =[Date] ?? Obviously I drop the sum function and the Revenue, but I can't get that expression to be the dimension.

I've tried both of these but I still get Invalid Dimension...

={<[Sheet1.Date]={">=$(=MonthStart(AddMonths(Max(Today()),-index)))<$(=MonthEnd(Max(Today())))"}> }

=<{">=$(=MonthStart(AddMonths(Max(Today()),-index)))<$(=MonthEnd(Max(Today())))"}>

The syntax is confusing  me.
Thank you!

[sunny_talwar]

Why do you drop the Sum() function? I am confused as to what you are trying to do? Why don't you use the same expression in the chart like your KPI expression?

[Jorich919]

I was dropping the sum() and trying to use that expression to limit the dates by using it as an expression for the Date dimension. I assume you are suggesting to use that line of code for the Revenue measure in the chart?

The challenge with that is that I have 2 measures being shown on my line chart, and I also have conditional set expressions to limit the Revenue being shown for each line.  For instance, this is one of the lines:

Sum( { <[Billing_Type]={'Credit Card'}> } Revenue)

Is there a way to incorporate my rolling period expression into that set expression for the Revenue measure? Is that the way to solve this?

[sunny_talwar]

Just add the set analysis on date to your current expression

Sum({<[Billing_Type] = {'Credit Card'}, [Date] = {">=$(=MonthStart(AddMonths(Max(Today()), -vRoll)))<$(=MonthEnd(Max(Today())))"}>} Revenue)