The use of variables to filter a date range

[#1]

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.

[Miguel_Angel_Baeyens · Accepted solution]

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

[vincent_ardiet]

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

[#4]

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

[#5]

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

[krishnaroopa]

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