Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

The Master Calendar

One of the most common problems to solve in data modeling is that of time. How to assign month name and other time attributes to a date. The solution is called a master calendar.

A master calendar table is a dimensional table that links to a date in the data, e.g. OrderDate.  The table usually does not exist in the database, but is nevertheless needed in the QlikView application for a proper analysis. In the master calendar table you can create all time and date fields that you think the user needs; e.g. Month, Year, Quarter, RollingMonth, FiscalMonth or flags like IsCurrentYear, etc.

 

A typical master calendar tables contains one record per date for the time period used in the QlikView app, perhaps a two-year period, i.e. 730 records. It is in other words a very small (short) table. Since it is small, you can allow yourself to have many fields in it – it will not affect performance in any significant way.

 

There are in principle three ways you can generate the records (with an infinite number of variations in the details):

  • Load from the fact table, e.g.
    Load distinct Date, Month(Date) as Month resident TransactionTable ;
  • Generate all dates within a range, using autogenerate, e.g.
    Load Date, Month(Date) as Month … ;
    Load Date($(vStart) + RecNo()) as Date autogenerate $(vEnd) - $(vStart) ;
  • Generate all dates within a range, using a while loop, e.g.
    Load Date, Month(Date) as Month … ;
    Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ;
    Load Min(Date)-1 as MinDate, Max(Date) as MaxDate resident TransactionTable

 

In the first case you use the table to which you are going to link the master calendar. This way you will get exactly those values that really exist in the database. Meaning that you will also miss some dates – e.g. Saturdays and Sundays most likely - since they often do not exist in the database.

 

In the second case, you generate a range of dates. This is a good solution, but it means that you will need to define the range beforehand. There are several ways to do this, e.g. find largest and smallest value in the data; or hard-code the days for the relevant year.

 

In the third solution, you generate all dates between the first and last date of your transaction table. This is my preferred solution. Optionally you can use YearStart(Min(Date)) and YearEnd(Max(Date)) to define the range.

 

Data Model.png

 

The word "Master" for the calendar table is really misleading. There is no reason to have only one calendar table. If you have several dates, you should in my opinion use several calendar tables in the same data model. The alternative - to have the same calendar for all dates - is possible using a link table but complicates the data model and limits how the user can make selections. For example, the user will not be able to select OrderMonth=’Sep’ and at the same time ShipperMonth=’Nov’.

 

Bottom line: Use a calendar table whenever you have a date in your database. Use several if you have several dates.

 

HIC

32 Comments
Partner
Partner

Thank you very much HIC!!

This is what I was looking for. As always nice article from you.

0 Likes
61 Views
yurgelmartina
New Contributor

Hi Henric,

I am trying to do a Master Calendar for forecast adjustment.

My issue is that I have a table for forecast in months and I need to adjust for daily curve based on 2017.

The trick here is that we have holidays that need to be adjusted. For example we had Mothers Day at 14/05/2018 and 13/05/2017.

Do you have a solution or tutorial to split it correctly?

KEY, FORECAST: "Ano Mes"&DCO&StoreLocation,


KEU SALES: AnoMes&DtMov&DCO&StoreLocation

Code:

//Holiday Calendars for 2017 e 2018


Holidaystmp:

Load * INLINE [

HolidayDate,HolidayDesc,DayDescription

01/01/2018,Confraternização Universal 2018,segunda

01/01/2018,Ano Novo 2018,segunda

06/01/2018,Dia de Reis 2018,sábado

02/02/2018,Dia de Iemanjá 2018,sexta

13/02/2018,Carnaval 2018,terça

14/02/2018,Cinzas 2018,quarta

14/02/2018,Dia de São Valentim 2018,quarta

18/02/2018,Horario de inverno 2018,domingo

08/03/2018,Dia da Mulher 2018,quinta

25/03/2018,Semana Santa 2018,domingo

25/03/2018,Domingo de Ramos 2018,domingo

26/03/2018,Segunda-feira Santa 2018,segunda

27/03/2018,Terça-feira Santa 2018,terça

28/03/2018,Quarta-feira Santa 2018,quarta

29/03/2018,Quinta-feira Santa 2018,quinta

30/03/2018,Sexta-feira Santa 2018,sexta

30/03/2018,Paixão de Cristo 2018,sexta

31/03/2018,Sábado Santo 2018,sábado

01/04/2018,Dia da Mentira 2018,domingo

01/04/2018,Páscoa 2018,domingo

21/04/2018,Tiradentes 2018,sábado

22/04/2018,Descobrimento do Brasil 2018,domingo

23/04/2018,Dia de São Jorge 2018,segunda

01/05/2018,Dia do Trabalhador 2018,terça

13/05/2018,Dia das Mães 2018,domingo

20/05/2018,Pentecostes 2018,domingo

31/05/2018,Corpus Christi 2018,quinta

31/05/2018,Dia do Espírito Santo 2018,quinta

12/06/2018,Dia dos Namorados 2018,terça

24/06/2018,Dia de São João 2018,domingo

20/07/2018,Dia do Amigo e Internacional da Amizade 2018,sexta

20/07/2018,Dia da 1ª Viagem à Lua 2018,sexta

12/08/2018,Dia dos Pais 2018,domingo

07/09/2018,Independência do Brasil 2018,sexta

12/10/2018,Nossa Senhora da Conceição Aparecida 2018,sexta

12/10/2018,Padroeira do Brasil 2018,sexta

12/10/2018,Dia das Crianças 2018,sexta

21/10/2018,Horario de verão 2018,domingo

02/11/2018,Finados 2018,sexta

15/11/2018,Proclamação da República 2018,quinta

28/11/2018,Dia Mundial de Ação de Graças 2018,quarta

25/12/2018,Natal 2018,terça

01/01/2017,Confraternização Universal 2017,domingo

01/01/2017,Ano Novo 2017,domingo

06/01/2017,Dia de Reis 2017,sexta

02/02/2017,Dia de Iemanjá 2017,quinta

14/02/2017,Dia de São Valentim 2017,terça

19/02/2017,Horario de inverno 2017,domingo

28/02/2017,Carnaval 2017,terça

01/03/2017,Cinzas 2017,quarta

08/03/2017,Dia da Mulher 2017,quarta

01/04/2017,Dia da Mentira 2017,sábado

09/04/2017,Semana Santa 2017,domingo

09/04/2017,Domingo de Ramos 2017,domingo

10/04/2017,Segunda-feira Santa 2017,segunda

11/04/2017,Terça-feira Santa 2017,terça

12/04/2017,Quarta-feira Santa 2017,quarta

13/04/2017,Quinta-feira Santa 2017,quinta

14/04/2017,Sexta-feira Santa 2017,sexta

14/04/2017,Paixão de Cristo 2017,sexta

15/04/2017,Sábado Santo 2017,sábado

16/04/2017,Páscoa 2017,domingo

21/04/2017,Tiradentes 2017,sexta

22/04/2017,Descobrimento do Brasil 2017,sábado

23/04/2017,Dia de São Jorge 2017,domingo

01/05/2017,Dia do Trabalhador 2017,segunda

14/05/2017,Dia das Mães 2017,domingo

31/05/2017,Dia do Espírito Santo 2017,quarta

04/06/2017,Pentecostes 2017,domingo

12/06/2017,Dia dos Namorados 2017,segunda

15/06/2017,Corpus Christi 2017,quinta

24/06/2017,Dia de São João 2017,sábado

20/07/2017,Dia do Amigo e Internacional da Amizade 2017,quinta

20/07/2017,Dia da 1ª Viagem à Lua 2017,quinta

13/08/2017,Dia dos Pais 2017,domingo

07/09/2017,Independência do Brasil 2017,quinta

12/10/2017,Nossa Senhora da Conceição Aparecida 2017,quinta

12/10/2017,Padroeira do Brasil 2017,quinta

12/10/2017,Dia das Crianças 2017,quinta

15/10/2017,Horario de verão 2017,domingo

02/11/2017,Finados 2017,quinta

15/11/2017,Proclamação da República 2017,quarta

28/11/2017,Dia Mundial de Ação de Graças 2017,terça

25/12/2017,Natal 2017,segunda

];

Holidays:

NoConcatenate

load

date(HolidayDate) as HolidayDate,

HolidayDesc,

DayDescription

resident Holidaystmp;

drop table Holidaystmp;


HolidayCalendar:

Load

Chr(39) & Replace(Concat(HolidayDate,','),',',chr(39) & ',' & chr(39)) & chr(39) as HolidayList

resident Holidays;


Let vHolidayList = Peek('HolidayList',0,'HolidayCalendar');


//Define the range of dates you want to include

Let vMinDate = num(Date('01/01/2017','MM/DD/YYYY'));

Let vMaxDate = Num(Date(Today(),'MM/DD/YYYY'));

Let vToday = num(Today());


//Autogenerate a source table for your calendar

TempCalendar:

Load

$(vMinDate) + RowNo() - 1 as Num,

Date($(vMinDate) + RowNo() - 1) as TempDate

Autogenerate

$(vMaxDate) - $(vMinDate) + 1;


MasterCalendarTemp:

Load

    date(TempDate) as OrderDate,

    Week(TempDate) as Week,

    Year(TempDate) as Year,

    Month(TempDate) as Month,

    Day(TempDate) as Day,

    WeekDay(TempDate) as WeekDay,

    'Q' & ceil(Month(TempDate)/3) as Quarter,

    Date(monthstart(TempDate),'MMM-YYYY') as MonthYear,

    Week(TempDate) & '-' & Year(TempDate) as WeekYear,

    inyeartodate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

    inyeartodate(TempDate,$(vToday),-1) * -1 as LastYTDFlag,

    NetworkDays(TempDate,TempDate,$(vHolidayList)) as IsWorkingDay

Resident TempCalendar

order by TempDate ASC;


Left Join (MasterCalendarTemp)

Load

    HolidayDate as OrderDate,

    HolidayDesc,

    DayDescription

Resident

Holidays;


MasterCalendar:

NoConcatenate

Load * Resident MasterCalendarTemp

order by OrderDate ASC;

;

STORE MasterCalendar  INTO [lib://MasterCalendar/MasterCalendar.csv](txt,delimiter is ';');

drop table Holidays;

drop table HolidayCalendar;

drop table TempCalendar;

drop table MasterCalendarTemp;


//Forecast to 2018


LOAD

    KEY,

    "Ano Mes",

    right("Ano Mes",2) as Month,

    left("Ano Mes",4) as Year,

    left("Ano Mes",4)&'/'&right("Ano Mes",2) as YearMonth,

//     Mês,

    "Grupo Compras",

    "GC Description",

    DCO,

    "DCO Description",

    StoreLocation,

    "StoreLocation Description",

    "Ven. Fís. Plan.",

    "Ven. Fís. Proj.",

    "Ven. Fís. Prev."

FROM [lib://QVD/Input\Forecast\Venda Planejada x Prevista x Projetada.xlsx]

(ooxml, embedded labels, table is [Venda Planejada x Prevista x Pr]);


//2017 Sales by Day.


LOAD

    CodFil as StoreLocation,

    CodDepto as DCO,

    DtMov, //Data

    AnoMes,

    VendaFis,

    VendaFin,

    VendaLiq,

    CustoLiq

FROM [lib://QVD/Input\Vendas\BASE_VENDA2017-DCOLojaAno.txt]

(txt, codepage is 28599, embedded labels, delimiter is ',', msq);


exit script

0 Likes
61 Views