topic Re: Filtering Dates : Dual Columns [From] & [To] in QlikView

Filtering Dates : Dual Columns [From] & [To]

simonb2013 — Fri, 14 Feb 2014 08:47:29 GMT

Firstly 'Hello' !

Secondly, my first QV challenge.....

I have a table box with 2 columns, dateFrom & dateTo.

What I am trying to achieve is for the user to select a month (e.g. March 2013), and my table will filter where (dateFrom < selectedMonthDate) AND( dateTo > selectedMonthDate)

I also have a calendar table populated with a range of months (1st of every).

Yet I am struggling to see how to establish the mechanism for my calculated filter.

Of course, being new, I might be tackling this from completely the wrong angle also !

Any pointers. relevant tutorials, comments or advice appreciated !

Many Thanks

Re: Filtering Dates : Dual Columns [From] & [To]

hic — Fri, 14 Feb 2014 08:51:09 GMT

Basically you want to make the selection on a reference date that is between the dateFrom and the dateTo. The best solution for this is to create this reference date in the script. I describe how to do this in the following blog post:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

HIC

Re: Filtering Dates : Dual Columns [From] & [To]

simonb2013 — Fri, 14 Feb 2014 08:59:29 GMT

Hi Henric, and thanks for the extremely swift reply !

I've just read that start of your article, and if I read correctly, I'd basically be creating cross join table.

i.e. 1 record for every month of (say) 5 years, for every item in my main table.

numberoforiginaldata rows * 12 * 5

and if I wanted the user to select any day of the year over 5 years... numberoforiginaldataRows * 365 * 5

I may be double checking here because I am from a DB background where that would be considered bad practice .... but I also realise maybe I have to change my mindset now in the QV world !

Many Thanks

Re: Filtering Dates : Dual Columns [From] & [To]

hic — Fri, 14 Feb 2014 09:04:12 GMT

Your'e absolutely right that this is similar to a cross join. And that it is bad practice when it comes to storing data.

But here we have a case where the goal is to allow analysis, to make it simpler for the user, and to decrease response times. If you then can trade some memory for response time (use more memory in order to decrease response time), you should absolutely do it.

HIC

Re: Filtering Dates : Dual Columns [From] & [To]

simonb2013 — Fri, 14 Feb 2014 09:06:38 GMT

Ok, I'm going for it !

Many Thanks