How to use - Master-Calendar and Date-Values

    Dimensions and calculations over periods are essential parts from nearly each reporting. The analysis from data regarding to periods is quite independent from the to analyse data-area regardless if this are sales-, finance-, human-ressources- or production-data. Nearly nothing is more exciting within an analysis as the development from data over the time and the questions which are following like: Was these development expected or not and what could be the reasons?


    However the handling from time-data could be difficult whereas the most could be avoided with a few simple rules.


    The easiest way is often to use a master-calendar as dimension-table which is linked to the fact-table(s). Why and how, see:


    Use of Master Calendar

    The Master Calendar

    The Master Time Table

    The Fastest Dynamic Calendar Script (Ever)

    Master Calendar with movable holidays


    In more complex data-models is it often necessary to create several calendars and/or to use calendars which are divergent to normal year-calendars.


    Canonical Date

    Why You sometimes should Load a Master Table several times

    Fiscal Year

    Fiscal Calendar with Non-Standard Days (Not 1-31)

    4-5-4 Master Calendar

    Non-Gregorian calendars

    Redefining the Week Start

    Redefining the Week Numbers



    Important is to define and formate the time-dimension properly. Properly meant that the dimensions are (also) numeric then only numeric values could be calculated respectively compared with each other.

    Background is that the date of 12/31/1899 is equal to 1 and each further day will be added by one so that the date of 12/31/1999 corresponds to 36525. Hours/Minutes/Seconds are fractions from 1, for example 1 / 24 / 60 = 0,000694 is equal to 1 minute.


    This meant that all fields which should be calculated (comparing is calculation, too) should be (additionally) available as numeric field or as Dual-Field:


    Get the Dates Right

    The Date Function

    Why don’t my dates work?


    Often are additionally relative and/or continuing time-dimensions and flags very helpful to avoid complex calculations:


    Relative Calendar Fields

    Creating Reference Dates for Intervals

    Calendar with period flags

    Calendar with flags making set analysis so very simple

    Period Presets: Compare Periods on the fly


    The As-Of Table

    Subroutine to Create Data Model for From/To Date Selection

    Calendar with AsOf Flags, Compare Easter to Easter


    Beside simple but frequent time-comparing with one or several time-dimensions in one object and simple expressions like sum(value) or count(order) are there more complicated questions like:


    Year-over-Year Comparisons

    Previous YTQ, QTD, MTD and WTD

    Calculating rolling n-period totals, averages or other aggregations


    Beside the above used links you will find many interessting postings here within the qlik community to these topic - the notes here are a good starting point to go further.


    Have fun!


    Marcus Sommer


    ps: within the attachment is a german translation - deutsche Fassung.