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.
