Generate date ranges between two fields

[Dayna]

Hello All,

A small daft question, I have two date fields - opendate and closedate, and I would like to generate a range of dates for use in charts etc to show the min(opendate) - today().

How would I do this? And preferably to do it in the table where my data is stored or a method to link to my existing data...?

Kind Regards,
Dayna

[brenner_martina]

Hi Dayna,

what does opendate - today() means, the days will be negative? And what do you mean with a range of dates? You can use the class-function to group dates by a fix number of days.

[#3]

please do this script

date(today(),'DD-MM-YYYY')-date(min(opendate),'DD-MM-YYYY')

Regards

Ashish Srivastava

[Dayna]

Hello,

I think you misunderstand my request, I don't want the dates to be grouped, I need to fill in the missing blank dates so they could be displayed on a chart. Opendate is simply the name of my field containing the date. So I would like to have a selector with all dates from the minimum opendate to today's date.

So if my date I have is 31/3/10 I would like to have a date for every day from this to today's date.

Kind Regards,
Dayna

[#5]

Hi,

This is a recurring need in the BI world : to have a timetable.

Why don't you automatically generate a calendar that you link to your table by the date field. In your graphs and tables you will use the objects (months, years, semesters, ...) that you've created in your calendar table.

I've never tested this solution on QlikView (i'm a new user) however i see no reason being that it doen't work.

Hope this can help you.

[#6]

Hello! Dayna,

Here is the link from there you can download qlikview cookbook.....by robwunderlich . this is free

http://robwunderlich.com/Download.html

in this u may find ur solution try to find "generate Date table " in the cookbook.qvw

hope it ll help you.....

Thank You

Patel

[johnw]

In addition to generating the dates, you also need to intervalmatch them back to your main table. I was going to just describe the process, but there's quite a bit to it, really, and some of it is tricky. So see attached.

[#8]

OD:

LOAD

Date('01/01/2011' + RowNo() - 1) AS DataStart,

Date('10/01/2011' + RowNo() - 1) AS DataEnd

AUTOGENERATE 3000;

FILTRO:

LOAD

Date('01/01/2011' + RowNo() - 1) AS Data1

AUTOGENERATE 3000;

Inner Join IntervalMatch (Data1) LOAD

DataStart, DataEnd

RESIDENT OD;

[bimanbeginner]

Hi John

Please could you advise how to do this when using a timestamp instead of a date, I tried replicating your qvw using the timestamp but I get an error saying ")" is expected when creating the date table- 1st line.i cant seems to see how to fix it