topic Re: The use of variables to filter a date range in QlikView

The use of variables to filter a date range

Mon, 10 Oct 2011 09:55:42 GMT

Hi there,

I'm new (ish) to Qlikview and have a question, which has been asked before and resolved, however it does not seem to work for me!!

I have a Qlikview report which I want to be able to filter on for a date range. The end user does not like the slider option and would just like to be able to input a from and to date and this should automatically filter the record set in a straight table or table box.

So, the method I've chosed is to create two variables; startdate & enddate.

I've created two calendar objects to populate the variables.

I have set up two variable trigger events onInput and onChange; The Action is to 'Select in Field" on column TRADE_DATE and the search string is

='>='&Date(STARTDATE)&'<='&Date(ENDDATE).

I have created a simple List Box which contains TRADE_DATE

Now, on the example I have downloaded, when inputting a date range I see in a Current Selections box <<date_column>> >=01/01/2010<=01/01/2011

which is what I want, however in my report nothing happens. Have I missed a step??

Here's the post I got the information from. http://community.qlik.com/message/125465#125465

Many thanks for your help.

Rob Walker.

Re: The use of variables to filter a date range

SunilChauhan — Mon, 10 Oct 2011 10:02:15 GMT

here you have two input variable

where user can enter their dates and

result will filter accordingly

Re: The use of variables to filter a date range

kaushiknsolanki — Mon, 10 Oct 2011 10:19:15 GMT

HI,

Have a look at the other example.

Hope this helps.

Regards,

Kaushik Solanki

The use of variables to filter a date range

Mon, 10 Oct 2011 10:23:11 GMT

Hi Sunil,

Thanks for the reply. It looks like this is run off a Macro. I would like to get the same effect without using a macro (as in the example in the hyperlink). It would appear that I'm missing a step even though I've set up the 2 variables, the calendar inputs for those variables and the trigger variable events.

Many thanks

The use of variables to filter a date range

kaushiknsolanki — Mon, 10 Oct 2011 10:40:51 GMT

HI,

Did you try the example i gave you.

Regards,

Kaushik Solanki

The use of variables to filter a date range

Mon, 10 Oct 2011 12:35:08 GMT

Still doesn't seem to link as I would like to my date field. I will add a sanitised version of my qvw.

Thanks.

The use of variables to filter a date range

Mon, 10 Oct 2011 13:37:53 GMT

I've uploaded the qvw. What I had originally were 2 variables (not QV recommended naming conventions I know!!) which the two calendar selections populated. I then had variable trigger events onInput & onChange and an Action on Select on field. On the example qvw from that post, when you select values for the dates the Current Selections contain the necessary filter, however on my qvw, the event doesn't seem to trigger?

I've also added your calculation to the TRADE_DATE list box using the values from the variables, and again updating the calendar objects doesn't seem to do anything....??

Re: The use of variables to filter a date range

Mon, 10 Oct 2011 13:39:05 GMT

Hi Kaushik,

I've added a cut down version of my qvw to my post.

Many thanks.

The use of variables to filter a date range

SunilChauhan — Mon, 10 Oct 2011 13:46:17 GMT

hello rob

its not a macro

only i did added two inputbox and added two dates field

Input box provide the facility to enter .so you can enter your date range

from date in one and to date in other .

The use of variables to filter a date range

Mon, 10 Oct 2011 14:11:41 GMT

Still doesn't seem to work on my qvw. I've attached it for your perusal. Are you able to see where I'm going wrong?

Many thanks

Re: The use of variables to filter a date range

Miguel_Angel_Baeyens — Mon, 10 Oct 2011 14:40:08 GMT

Rob,

Check the button "Apply Date Filter" I've just created in your same application to get the start and end date and apply that filter to the whole sheet. Please check the calendar objects, minum and maximum, where I've prefixed the expression Min() and Max() with the "=" sign.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: The use of variables to filter a date range

vincent_ardiet — Mon, 10 Oct 2011 14:59:55 GMT

Hi Rob,

I have seen the followings errors in your application :

- In the search string you have not put the hour, try with : ='>='&date(STARTDATE)&' 00:00:00<='&date(ENDDATE)&' 00:00:00'

- In the min/max boundaries for you calendar fields it's better not to take in account the current selection so try with : Max({1} TOTAL TRADE_DATE) and Min({1} TOTAL TRADE_DATE)

Regards,

Vincent

The use of variables to filter a date range

Mon, 10 Oct 2011 15:21:20 GMT

Chaps,

I think your suggestion on adding the time part to the date string fixes the issue. Presumeably the dataformats have to be exact matches (unlike in SQL where as long as something is a date, you don't have to include the time part for a greater or less than operator). Added the = sign to the min and max as well.

Many thanks

Re: The use of variables to filter a date range

Wed, 07 Mar 2012 18:09:26 GMT

Hi Miguel,

How would I modify this to allow me to see 2 years worth of data, all the way down to the day? I would like to show the current month, then the next 12 months. But in my calculation, I'm using data from 2011 and 2010. I've supplied how I want to see it and my sample app.

Any advice?

Alex

Re: The use of variables to filter a date range

krishnaroopa — Tue, 31 Jul 2012 07:32:14 GMT

I tried to use the same "Apply Date Filter' to filter date range.. Same expression.. but it is not working..