Dynamically Selecting Timeframes

[Jennell_McIntire]

How can a button be used in a QlikView app to dynamically select the last 3 months?  I was asked that question a few weeks ago and thought it would make a good blog topic.  It can easily be done and can provide the user with a quick way to filter data by a timeframe.  Now there are other ways of doing this besides using a button for instance List Boxes can be used to select the desired dates.  In this example, I have used a Text Object to do this.

In a Text Object, I add actions that clear certain fields and then make some selection(s).  But before I do that I create variables that will be used in the actions.  For example, if I wanted two Text Objects that select the last 7 days and the past 3 months, I would first create a variable that stores the current date (let’s assume the current date is July 1, 2014) and then add additional variables for the two timeframes: past 7 days and past 3 months.  I added the following to my script and reloaded it.

The script creates a variable, vTodaysDate, that stores the current date (July 1, 2014) in the appropriate date format (as set in the SET DateFormat=’M/D/YYYY’ line in the beginning of the script) and then creates variables for the past 7 days and past 3 months.  The value of the variables once the app is reloaded is as follows:

vTodaysDate = 7/1/2014

vPast7Days = 6/24/2014

vPast3Months = 4/1/2014

These variables are then used in the action of the Text Object to select the specified timeframe.  So to see how this will work, let’s review the actions that are used for the Past 7 days and the Past 3 months Text Objects seen in the image below.

The Past 7 days Text Object would have the following actions:

Action Sort Order	Action	Field	Search String
1	Clear Field	OrderDate
2	Clear Field	Year
3	Clear Field	Month
4	Clear Field	Quarter
5	Select in Field	OrderDate	=’>=$(vPast7Days)’

The OrderDate, Year, Month and Quarter fields are cleared and then order dates that are greater than or equal to 6/24/2014 are selected.

The Past 3 months Text Object would have the following actions:

Action Sort Order	Action	Field	Search String
1	Clear Field	OrderDate
2	Clear Field	Year
3	Clear Field	Month
4	Clear Field	Quarter
5	Select in Field	OrderDate	=’>=$(vPast3Months)’

The OrderDate, Year, Month and Quarter fields are cleared and then order dates that are greater than or equal to 4/1/2014 are selected.

Making these selections by clicking a Text Objects with actions can be easier than selecting multiple dates from an OrderDate List Box.  When the user has the need to view the data based on predefined timeframes, Text Objects or Buttons can be used to simplify the process.  You can find an example of how to create timeframe links in my technical brief that adds timeframe selections to the Dashboard sheet of the Sales Management and Customer Analysis demo.

Thanks,

Jennell

[ngulliver]

Came across this blog today. Love it. Nice clean solution clearly written.

[#3]

What would search string look like in case of multiple criteria?  I setup 2 variables similar to the example above and tried this in the search string for the Select In Field Action:

='>$(vLast) & <$(vToday)'

and does not seem to work.  Any suggestions?

[Jennell_McIntire]

Jaideep - removing the "&" should solve your problem: ='>$(vLast) <$(vToday)'

[#5]

Yes, that did it.  Thanks much. 

[datanibbler]

Hi,

that's very good. Straightforward and really practical.

I have only one question:

=> In the past, we sometimes had issues with several actions on some object being executed in a certain order (for instance, when we had the actions "open QlikView doc." and "close doc.", QlikView opened the doc in question - and closed it right away, so the effect was zip.

=> Has this been resolved in the newer versions?

[#7]

How can I implement this using a list box? In other words, how can I generate the Data Key and Data Label fields mentioned above?

[#8]

How can I generate the Data key and Data label fields mentioned above

[omerfaruk]

Hi,

Recently, I implemented the similar soln (DateKey and DateLabel). But there are issues with both approaches:

First,

In the current selection box, something like "OrderDate     >= 7 Oct 2014" doesn't look user friendly and doesn't exactly say that you are viewing last x months. The users so far got used to see this format: "OrderYear : 2014". and this is easy to grasp whilst above is not. After dealing with the technical bit, I think we should take care of the user friendliness of the implementation. So, following would be clearer for users to understand that they are viewing the orders given in the last 6 months:

"OrderDate   : Last 6 Months".

There should be a better solution which let users to see such an informing text in current selection box.

As an alternative solution to this visual issue, I preferred to create last x month flags using dual function in my calendar table, selected these flags and saved them as a bookmark. So, all last x period selections are available under bookmarks section. For the visual look, I set dual function text to 'selected', and it shows:

Rolling  12 Months  : Selected

An example of flag I created in my calendar table:

if(TempDate > addmonths(Today(),-12) and TempDate <= Today(), Dual('Selected', 12)) as [Rolling 12 Months],

Second, triggers one after another will cause significant delays at front end.

Another issue with the (DateKey and DateLabel) solution was, when "last x months" is selected in the list box, if you select a year, it takes you to the last x months in that year. Can you please confirm if this is not a concerning issue with the solution in the above blog post? What happens when you select a month in a previous year while last x month is selected?

[#10]

Nice one! that was exactly what I was looking for. Thanks for that idea!

[#11]

Im sorry people, I have downloaded the tachnigal brief and the qvw, and I'm working through it step by step, as i'm doing it for one of my modules. But one thing i'm not finding, is the vTimeframes expression?

am I just missing it completely? So now I am stuck as I cannot action the button without this.

Please assist