topic Re: 6 months data, 1 year Data and FROM to TO in QlikView

6 months data, 1 year Data and FROM to TO

Sat, 07 Jun 2014 08:08:38 GMT

Hi,

I have a field like INV_DATE in my data model and my requirement is to create three button objects.

Button 1: If I click it should filter last 6 months data (ex: today is 7th June, I am supposed to get Dec 2013 to May 2014)

Button 2: If I click it should filter last 1 year data (ex: today is 7th June, I am supposed to get June 2013 to May 2014)

Button 3: I should be able to pick as my wish like FROM to TO. It may be not a button, looking for other solution.

Is it possible to implement using only one INV_DATE filed?

Thanks,

Re: 6 months data, 1 year Data and FROM to TO

Gysbert_Wassenaar — Sat, 07 Jun 2014 08:48:32 GMT

Button 1 and 2 can be implemented with buttons. You can add Select in Field actions to the button to apply the selection in the field INV_DATE.

Button 3 is easiest done with a slider object.

See attached example.

Re: 6 months data, 1 year Data and FROM to TO

Sat, 07 Jun 2014 09:00:34 GMT

Unfortunately I have personal edition, pls copy script here. My guessing was right for 1 &2.

Thanks,

Re: 6 months data, 1 year Data and FROM to TO

Gysbert_Wassenaar — Sat, 07 Jun 2014 09:05:39 GMT

Here are the two search strings for the Select in Field actions for the two buttons

Search string for last six months:

='>' & num(addmonths(today(),-6)) & '<=' & num(today())

Search string for last year:

='>' & num(addyears(today(),-1)) & '<=' & num(today())

Note, make sure that your data field contains dates and not string values. Use the date# function if necessary in the script to make date values from string values.

Re: 6 months data, 1 year Data and FROM to TO

MK_QSL — Sat, 07 Jun 2014 11:32:31 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/06/dynamically-selecting-timeframes

Re: 6 months data, 1 year Data and FROM to TO

Sat, 07 Jun 2014 13:21:06 GMT

Hi,

I created a BUTTON with the name of Last 6 months, in Action tab Added SELECT in FIELD

In Field: INV_DATE

In search string: ='>' & num(addmonths(today(),-6)) & '<=' & num(today())

I guess before that I should clear all Date field selections. I did as in Action Clear Other Fields in Filed I have selected date fields.

But above code is not working and I suppose to get Dec 2013 to May 2013 as I mentioned in the first post.

Like the same way for Last 1 year too.

Thanks,

Re: 6 months data, 1 year Data and FROM to TO

Gysbert_Wassenaar — Sat, 07 Jun 2014 13:29:42 GMT

Try ='>=' & num(monthstart(today(),-7)) & '<' & num(monthstart(today()))

Re: 6 months data, 1 year Data and FROM to TO

Sat, 07 Jun 2014 14:29:00 GMT

I tried with this

='>=' & monthstart(today(),-6) & '<' & monthend(Date(today()),-1)

But button is not responding.

Thanks,

Re: 6 months data, 1 year Data and FROM to TO

Sat, 07 Jun 2014 18:17:58 GMT

Pls let me know where I am doing wrong.

Thanks,

Re: 6 months data, 1 year Data and FROM to TO

nikhilgarg — Mon, 22 Dec 2014 06:30:28 GMT

HEy,

Cann't we do like this :

Sum({<Date = {">= $(=addmonths(today(),-6))"}>}Sales) ??