How to use - Master-Calendar and Date-Values

[marcus_sommer]

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.

[NiceC]

Hi,

I would like to make a loop that will retrieve the data of the last 4 weeks via a sql request and store them in a qvd file per week.

Here is what a collegue did, but it doesn't work.

PLease help !!!!

set jour_calcul = today();

let ann = year ($(jour_calcul) - 45);

let ann_prec = $(ann) - 1;

let mois = month ($(jour_calcul) - 45);

// Chargt des fichiers

// -------------------

For j = 1 to 2

if j = 1 then

     let sem_ref = $(ann_prec) & '2';

     let sem_enr = $(ann) & '1';

     let deb =$(#mois);

else

     let sem_ref = $(ann) & '1';

     let sem_enr = $(ann) & '2';

     if $(#mois) < 7 then

let deb = 1;

else

     let deb = $(#mois) - 6;

end if

end if

For i = $(#deb) to 6

set filename = SEGM_$(sem_enr)_$(i).qvd;

segm:

SQL request (just an example)

store segm into $(filename) (qvd);

drop table segm;

next i

next j

[marcus_sommer]

You are not very specific what didn't work ... and of course this monthly routine couldn't work without any adjustments to a weekly routine. If you really want to adapt this I suggest that you used the debugger to see which values the variables have in each iteration of the loops - quite helpful is also often to use TRACE statements to plot these variables and counters to the progess-window and within the log-file.

Beside this I would quite probably query the last 4 weeks in once and run then a small loop over this resident table to create a weekly output.

- Marcus

[Dhivaharan]

Thank you for making such a wonderful compilation