Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More

How to create a Calendar

mov
Esteemed Contributor III

How to create a Calendar

Frequently asked are the questions about handling Dates - how to create a list of dates, how to create months, or quarters, how to sort month in the right order, etc. I hope the following notes will be helpful.

First step is usually to define Start and End dates of the calendar. I use variable Start, and End, which are defined according to the business rules. For example, hardcoded, from Jan 01, 2000 to Dec 31, 2010:

LET Start = num(makedate(2000,1,1));
LET End = num(makedate(2010,12,1));

Or, start of the year 3 years back to the end of the next year (5 year span):
LET Start = floor(YearStart(AddMonths(today(), -12*3)));
LET End = floor(YearEnd(AddMonths(today(), 12)));

Or, find max and min dates in one of your data tables, and use them (in this case I usually expand start to the year start and end to the year end):
tmp:
LOAD
min(date) as MinDate,
max(date) as MaxDate
RESIDENT tablename;
LET Start = floor(YearStart(peek('MinDate')));
LET End = floor(YearEnd(peek('MaxDate')));
Drop Table tmp;
Or whatever buseness rules there may be...

I usually have a variable number of days for convenience:
LET NumOfDays = End - Start + 1;

Next step is to create a list of dates in the numeric form:
Date_src:
LOAD
$(Start) + Rowno() -1 as DateId
AUTOGENERATE $(NumOfDays);

Now we can create whatever we want out of this:
Calendar:
LOAD
DateId, // just in case
date(DateId) as Date // it will be in format defined in your SET DateFormat=, or in your system format
day(DateID) as Day,
week(DateID) as Week,
month(DateID) as Month, // simple month name; it is dual - numeric and text
dual(month(DateID) & '-' & year(DateID),
year(DateID) & num(month(DateID), '00')) as MonthYear, // Month-Year format, dual
year(DateID) as Year,
weekday(DateID) as Weekday,
'Q' & ceil(month(DateID)/3) as Quarter, // in format Q1, Q2, Q3, Q4
dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID),
year(DateID) & ceil(month(DateID)/3) as QtrYear // Qn-Year, dual
// and whatever else you may want here...
RESIDENT Date_src;
Drop Table Date_src;

A good habit is to clean up, usually at the end of the whole script:


LET Start = null();
LET End = null();
LET NumOfDays = null();

Michael Solomovich

Comments
MVP & Luminary
MVP & Luminary
Excellent and useful article. Thanks!
0 Likes
Not applicable
Hi Michael, Personally I don't like variables and temporary tables, because you'll easily forget cleaning them up; here's a version of your calendar table using preceding loads: LOAD gen_date as key_OrderDate, year(gen_date) as Year, month(gen_date) as Month, day(gen_date) as Day; LOAD date(od_min + iterno() - 1) as gen_date WHILE iterno() <= od_max - od_min + 1; LOAD min(OrderDate) as od_min, max(OrderDate) as od_max RESIDENT Orders GROUP BY 1; The bottom LOAD statement (that gets executed _first_!) will load 1 record with the minimum and maximum values for OrderDate from some table Orders. The second LOAD statement will repeat loading the minimum date + a loop counter for each day in the date range (maxdate - mindate + 1), generating 1 record for each day in the date range containing only the date of that day. The last LOAD statement (which is on top) generates some derived values (year, month, day of month; you could add year/month, quarter, weekday etc. here) for each day-record loaded in the second step. To generate a dates table based on fixed boundaries, you just have to replace the min(OrderDate) and max(OrderDate) with some fixed date values. Regards, Martijn
0 Likes
mov
Esteemed Contributor III
Martijn, thanks for your reply, I think the exact way of doing things in QV is often a matter of personal preference. In your case, as you say, the advantage is the absence of variables and temp tables. My example, IMHO, is more transparent. And, I'm often using the Start and End variables through the script (yes, I know about the RIGHT KEEP and EXISTS... :-)). But what is more important, is that both examples are about exactly the same three steps: 1. Define boundaries. 2. Generate the list of dates in the numeric format. 3. Create fields in a variaty of date formats as needed. Regards, Michael
0 Likes
Not applicable
Miachel, I agree with you Arun
0 Likes
Not applicable
Thanks Michael ... this was very helpful. I will use this script often.
0 Likes
Not applicable
Its Really super regards Ashok
0 Likes
Not applicable
So, I tested this Calendar script in QlikView 9 personal and it is not working. I also tested the Calendar script from Martijn ter Schegget and it didn't load either. Both give a load error. Script line error: [TEMP_DATE]: LOAD date( date#('$STARTDATE','DD-MM-YYYY')-1 + recno() ,'DD-MM-YYYY') as DATE AUTOGENERATE (date#('$ENDDATE') - date#('$STARTDATE'))+1 I copied it correctly so the quotes aren't bad, but this is not working. QlikView is annoying in that it doesn't give you any clue as to which part of the this line failed. It would be nice if it was like a SQL script in that it not only showed you the line but also where in the line things started to go bad.
0 Likes
Not applicable
0 Likes
Not applicable
Sorry, guess hrefs don't work: My new and improved Calendar in QlikView 9 http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/
0 Likes
Not applicable
Thanks for your share!
0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2009-05-28 11:28 PM
Updated by:
mov