QlikView documentation and resources.
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
Useful. Thanks for share
Hi Michael,
How would you go about adapting this for a financial year starting April 1st every year?
Thanks,
Gethyn.
Very usefull thanx,
I also need the hours and minutes, what do I have to add for this??
Regards
Marc
Hey, try this script, for a more speedy and dynamic approach: The Fastest Dynamic Calendar Script (Ever)
Thanks Michael,
Very helpfull.
Regards,
Maxime
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
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;