Qlik Community

Qlik Sense Documents & Videos

Documents & videos about Qlik Sense.

Simple Master Calendars (Date & Time)

Partner
Partner

Simple Master Calendars (Date & Time)

I'll share these scripts as they are very often requested and useful both in simple and advanced QlikView / Qlik Sense applications.


Please also look for other kind of master calendars that take advantage of flags to solve some of the most commonly used expressions.

  • Date Calendar

LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -1)));
LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));

DATE_CALENDAR:
LOAD
     CALENDAR_DATE AS %KEY_CALENDAR_DATE,
     DATE(CALENDAR_DATE) AS CALENDAR_DATE,
     YEAR(CALENDAR_DATE) AS CALENDAR_YEAR,
     DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS CALENDAR_MONTH,
     NUM(MONTH(CALENDAR_DATE)) AS CALENDAR_MONTH_NUM,
     NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS CALENDAR_YEAR_MONTH_NUM,
     DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS CALENDAR_WEEK_ORDER,
     DAY(CALENDAR_DATE) AS CALENDAR_DAY,
     DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS CALENDAR_WEEK_DAY,
     'Q' &
CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS CALENDAR_QUARTER,
     DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
          NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
     DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
          YEAR(WEEKSTART(CALENDAR_DATE)) & NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00')) AS CALENDAR_WEEK;
LOAD
     (
$(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);

SET S_DATE_START=;
SET S_DATE_END=;


  • Time Calendar

LET S_INTERVAL_MINUTES = 15;

TIME_CALENDAR:
LOAD
     CALENDAR_TIME AS %KEY_CALENDAR_TIME,
     CALENDAR_TIME,
     HOUR(CALENDAR_TIME) AS CALENDAR_HOUR,
     MINUTE(CALENDAR_TIME) AS CALENDAR_MINUTE;
LOAD
     TIME(TIME#(RECNO() * $(S_INTERVAL_MINUTES), 'm')) AS CALENDAR_TIME
AUTOGENERATE ((24*60) / $(S_INTERVAL_MINUTES)) - 1;

LET S_INTERVAL_MINUTES=;


Diego F. Caivano.-

Version history
Revision #:
1 of 1
Last update:
‎2016-02-12 03:36 PM
Updated by: