topic Re: Generate date ranges between two fields in QlikView

Generate date ranges between two fields

Dayna — Tue, 10 Aug 2010 10:26:21 GMT

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

AW:Generate date ranges between two fields

brenner_martina — Tue, 10 Aug 2010 10:49:21 GMT

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.

Generate date ranges between two fields

Tue, 10 Aug 2010 10:55:30 GMT

please do this script

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

Regards

Ashish Srivastava

AW:Generate date ranges between two fields

Dayna — Tue, 10 Aug 2010 10:58:12 GMT

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

Re. :Re: AW:Generate date ranges between two fields

Tue, 10 Aug 2010 16:59:31 GMT

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.

Generate date ranges between two fields

Tue, 10 Aug 2010 20:32:53 GMT

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

Generate date ranges between two fields

johnw — Tue, 10 Aug 2010 21:37:56 GMT

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.

Re: Generate date ranges between two fields

Wed, 30 Jan 2013 16:28:25 GMT

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;

Re: Generate date ranges between two fields

bimanbeginner — Wed, 14 Jun 2017 06:34:17 GMT

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