Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
please do this script
date(today(),'DD-MM-YYYY')-date(min(opendate),'DD-MM-YYYY')
Regards
Ashish Srivastava
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
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.
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
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.
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;
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