Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Dynamically Selecting Timeframes

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.

script.png

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.

popup.png

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

Action Sort OrderActionFieldSearch String
1Clear FieldOrderDate
2Clear FieldYear
3Clear FieldMonth
4Clear FieldQuarter
5Select in FieldOrderDate=’>=$(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 OrderActionFieldSearch String
1Clear FieldOrderDate
2Clear FieldYear
3Clear FieldMonth
4Clear FieldQuarter
5Select in FieldOrderDate=’>=$(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

22 Comments
Not applicable

Thanks.

This is a really great idea,  I had been using a version from Rob Wunderlich which involves a interval match against the master Calendar, but the advantage of your version is I can offer the best ranges for the dashboard section in question.

The attached application is a treasure trove of good design idea.

I will have a detailed look at that. (Looks like I have a long way to go in dashboard design)

Best regards

Richard

861 Views
Not applicable

Thanks for the idea!

861 Views
Marcio_Campestrini
Valued Contributor

Jennell, it's really a good idea. I'll start this in my applications ASAP.

Thanks.

861 Views
Luminary
Luminary

Jennell - This is smart solution and simple to implement. I'm keen to see the performance implications compared to Flag & Set Analysis approach. I'll start using this approach. Thank you for sharing!

Cheers,

DV

www.QlikShare.com

861 Views

Thank for the innovative idea.

really good post.

Ashfaq

861 Views
Not applicable

well explained.

0 Likes
861 Views
thornofcrowns
Valued Contributor II

Nice, that will come in very useful for me.

0 Likes
861 Views
Partner
Partner

Hi Jennell, nice article!

I also develop my applications using these kind of stuff you mentioned, but I've played with QV.Next v0.9 and I realised that it doesn't have actions. Can you tell us if QV.Next v1.0 will be released with or without Actions?


0 Likes
861 Views
Partner
Partner

Thanks Jennell, I tend to do this by generating another table in the data model that links dates from the rest of the model with a label. ie. a 2 field table (DateKey and DateLabel). So for example there would be 7 records where DateLabel= 'Past 7 Days' and DateKey would range between 1/7/2014 and 25/6/2014.  Then another 30 records for the 'Past 30 Days' etc. You can put in as many of these as you'd like. That way the DateLabel field can be used as a list box, it performs well as it's linked to the model, and the developer doesn't have to manage Actions or filters of the other date fields.

861 Views
Not applicable

Nice article, it is very useful to offer the user's different time frames. Just one more thing, I would insert today`s date ( xxx and <=Today) into the search string too, because it could be, that there exist future date values.

0 Likes
861 Views
Partner
Partner

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

0 Likes
861 Views
Not applicable

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?

0 Likes
861 Views
Employee
Employee

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

861 Views
Not applicable

Yes, that did it.  Thanks much. 

0 Likes
861 Views
datanibbler
Esteemed Contributor

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?

0 Likes
861 Views
Not applicable

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?

0 Likes
861 Views
Not applicable

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

0 Likes
861 Views
omerfaruk
Contributor

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?

861 Views
Not applicable

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

0 Likes
861 Views
Not applicable

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

0 Likes
861 Views
Employee
Employee

Hi Gerald,

The vTimeframe variable is created in the Variable Overview (Settings > Variable Overview).  Click the Add button to create the variable and then set it to 0.  There is an action on the Select timeframe button that sets the vTimeframe variable to 1 when the button is clicked.  This controls the show/hide of the select timeframe pop-up layer.  When it is 1, the layer is visible.  When the layer is closed, there is an action that sets the vTimeframe variable back to 0 thus hiding the layer again.

I hope this helps.

Jennell

0 Likes
861 Views
Not applicable

great idea

0 Likes
861 Views