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
Did you mean:

## 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

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)

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

Period Presets: Compare Periods on the fly

The As-Of Table

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.

Labels (4)

• ### Scripting

Contributor

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.

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

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

Partner - Contributor II

Thank you for making such a wonderful compilation

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