Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
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?
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?
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!
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?
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?
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)