topic date ranges in QlikView

date ranges

Mon, 29 Nov 2010 17:35:09 GMT

I have a table with products that has START_DATE and END_DATE fields. I also have many other related tables with the same START_DATE and END_DATE. I need to have an ability to filter and count my items depending on a specific Year and a specific Quarter. I am thinking to have in inline table that would identifies Years and Quarter(how?) and then link it somehow (how?) to the real tables. But I need to know that a chosen Year is inside of the range between START_DATE and END_DATE. Any Ideas how to do that?

Thanks a lot.

date ranges

Miguel_Angel_Baeyens — Mon, 29 Nov 2010 17:53:59 GMT

Hello,

Take a look at this file and see if that fits into your requirements. In short, I've used two slider objects one for the starting date the other for the end date, that store both values into variables. Then these variables are used in the expressions as a part of a set analysis.

Hope that helps.

date ranges

Mon, 29 Nov 2010 18:23:14 GMT

HI,

If you work into your code:

Year (***Yourdatefield***) AS Year,

Month

 (***Yourdatefield***) AS Month, [\code]
Then you can define with an inline
month, Qtr
Jan,1,
Feb,1,
March,1,.....etc
Then you can use the Qtr and Year field as a selection. If you concatenate the year and qtr (201001,201002,201003....) then you can select more than one quarter over more than one year in your slections. Otherwise, if you select Q1 and 2009 &2010, You'll be selecting Q1 from both years.
- Ronnie

 </body>

date ranges

Mon, 29 Nov 2010 19:28:13 GMT

Thank you. It is a big help. Still in my main table I have two fields Start_date and End_date. For instance start_date is 1/1/2007 and end date is 5/8/2008. When I need to know how many products were available on 2/2/2007 - I would need to include this in my count and filter product table as the 2/2/2007 is in the range. But if i need to know how many were available on 2/2/2010 I would not include this in the calculation

Thank you

date ranges

Mon, 29 Nov 2010 19:35:34 GMT

It looks to me that I would need to dynamically create a cross reference table between my product table and master calendar table. This new table would need to have an Product id and Master calendar id udder condition that the start date and end date of the product provide the range where a particular date from the master calendar falls into.

I think I have a good idea but do not know how to write it correctly. Thank you.

date ranges

Miguel_Angel_Baeyens — Mon, 29 Nov 2010 22:04:00 GMT

Hello,

You have several examples in the forum, particularly

http://community.qlik.com/wikis/qlikview-wiki/how-to-create-a-calendar.aspx

and

http://community.qlik.com/forums/p/22821/112779.aspx

Hope this helps

date ranges

Tue, 30 Nov 2010 21:15:15 GMT

Thank you for the links. I used the calendar and IntervalMatch to create a join table betwen calendar and my product table with the start and end dates.

IntervalMatch

( CalendarDate ) load startdate, enddate resident product;