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

    Calendars

     

    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.