Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

How to create a Calendar

cancel
Showing results for 
Search instead for 
Did you mean: 
mov
Employee
Employee

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
Not applicable

Useful. Thanks for share

0 Likes
Gethyn
Creator
Creator

Hi Michael,

How would you go about adapting this for a financial year starting April 1st every year?

Thanks,

Gethyn.

0 Likes
Not applicable

Very usefull thanx,

I also need the hours and minutes, what do I have to add for this??

Regards

Marc

0 Likes
tseebach
Partner
Partner

Hey, try this script, for a more speedy and dynamic approach: The Fastest Dynamic Calendar Script (Ever)

0 Likes
Not applicable

Thanks Michael,

Very helpfull.

Regards,

Maxime

0 Likes
mohan2391
Creator II
Creator II

Hi

I want 2 date filters in my app as 1 for selecting Start Date & 1 for selecting End Date. So that, the data belongs to that particular period will be displaying. I did it in Qlikview by using 2 calendar objects & also did by using Input Box object.

But here in Qlik Sense, how can i filter date ?

Pls help me....

Thanks in Advance

0 Likes
Shisho_Karsenty
Contributor III
Contributor III

Fix-ed script:

LET Start = num(makedate(2000,1,1));
LET End = num(makedate(2020,12,31));
LET NumOfDays = End - Start + 1;


Date_src:
LOAD
$(Start) + Rowno() -1 as DateID
AUTOGENERATE $(NumOfDays);

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
	year(DateID) 		as Year,
	weekday(DateID) 	as Weekday,
	dual(month(DateID) & '-' & year(DateID), year(DateID) & num(month(DateID), '00')) as MonthYear, // Month-Year format, dual
	'Q' & ceil(month(DateID)/3) as Quarter, //Q1,Q2,Q3,Q4 
	dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID), year(DateID) & ceil(month(DateID)/3)) as QtrYear // Qn-Year, dual // Q1-2019
RESIDENT Date_src;
Drop Table Date_src;

 

Version history
Last update:
‎2009-05-28 11:28 PM
Updated by:
Employee