Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

How to use - Master-Calendar and Date-Values

cancel
Showing results for 
Search instead for 
Did you mean: 
marcus_sommer

How to use - Master-Calendar and Date-Values

Last Update:

Sep 20, 2022 1:53:53 PM

Updated By:

Sue_Macaluso

Created date:

Jun 27, 2015 1:21:43 PM

Attachments

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.

Comments
eduardo_sommer
Partner - Specialist
Partner - Specialist

Nice and useful compilation

Thanks

0 Likes
john9inno
Creator
Creator

thanks so much for valuable input

0 Likes
Colin-Albert

A great resource Marcus.

I'm sure this will become a "Go To" document for date questions on the Community.

Not applicable

Great tool, amazing!! thanks so much.

0 Likes
ajsjoshua
Specialist
Specialist

Great Work marcus_sommer

0 Likes
Anonymous
Not applicable

Thanks for the great resource

Regards,

Sílvia

0 Likes
vinieme12
Champion III
Champion III

I've made my own transformation of the master calendar for comparing periods without comparing dates!

Period Presets: Compare Periods on the fly

0 Likes
yadhunandhanan
Partner - Contributor III
Partner - Contributor III

Thanks for the resource.

0 Likes
sudeepkm
Specialist III
Specialist III

this is good stuff very useful. Thanks so much.

0 Likes
ahaahaaha
Partner - Master
Partner - Master

Thanks for the info.

0 Likes
Version history
Last update:
‎2022-09-20 01:53 PM
Updated by: