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.-