How to Create Master Calendar ?

[jonesbrown]

Hi All,

I am new to Qlikview

How to Create Master Calendar ?

Please Help.

[m_woolf]

MasterCalendar:

Load

DateId as OrderDate,

week(DateId) as Week,

Year(DateId) as Year,

Month(DateId) as Month,

Day(DateId) as Day,

ApplyMap('QuarterMap',Month(DateId),null()) as Quarter,

week(weekstart(DateId)) & '-' & WeekYear(DateId) as WeekYr,

weekday(DateId) as WeekDay,

if(month(date(DateId))<4, year(date(DateId))-1983, year(date(DateId))-1982) As Period;

// Generate a temp table of dates

Load

mindate + Iterno() as DateId

While mindate + IterNO() <= maxdate;

// Get the min and max dates from the field

Load

min(FieldValue('OrderDate',recno())) - 1 as mindate,

max(FieldValue('OrderDate',recno())) as maxdate

Autogenerate FieldValueCount('OrderDate');

[Gopi_E · Accepted solution]

Hi Jones,

Master calendar is a table in a QlikView/Qlik Sense data model which generally contains

date related columns

Procedure:

1. Extract the Minimum and Maximum dates from a date column in the fact table. From the existing table, you create min and max.

     Min_Max:

     Load

     Min(<Date Field>) as MinDate,

     Max(Date Field) as MaxDate

     Resident <Table Name>

2. Populate all the dates between the min and max dates using peek function.

     Let vMinDate = Peek('MinDate',0,'Min_Max');

     Let vMaxDate = Peek('MaxDate',0,'Min_Max');

     Note: after that drop Min_Max temporary table.

3. Extract all the necessary date columns required for the dashboards

     Pulling dates between Min date and MAx Date

     Master_Calendar:

     Load *,

     MonthName(DateField)                    as MonthName,

     QuarterName(DateField)                  as QuarterName,

     YearName(DateField)                     as YearName,

     'Q'&Ceil(Month(DateField)/3) as Quarter,

     Year(DateField) as Year,

     Month(DateField)            as Month,

     WeekDay(DateField)                      as WeekDay;

          etc....................

4. Associate the date column in the fact table to the date column in the master calendar.

Load

Date($(vMinDate) + IterNo() - 1)    as <DateField>

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

This is one of the processes for creating a master calendar.

[jmmolero]

Hi Jones,

There are a lot of posts and blogs talking about "Master Calendar", doing a simple search on google you will find the answer:

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

Creating A Master Calendar

QlikView Tutorial | Creating Master Calendar in QlikView | Data & Tools - YouTube

[Anonymous · Accepted solution]

Hi Jones,

Master Calendar table is generally created to provide additional date fields like Quarter, Day, etc from an already existing date field. You can go through the below links for more information on creating master calendar, its advantages and many more.

https://www.tutorialspoint.com/qlikview/qlikview_master_calendar.htm

http://www.learnallbi.com/master-calendar-in-qlikview/

A video on how to create a Master Date Calendar in QlikView.

https://community.qlik.com/thread/48693

Advantages

https://community.qlik.com/thread/158524

Hope these links give you the required information.

Thanks,

Sarat.K

[pooja_prabhu_n · Accepted solution]

Hi,

refer this link

Master Calendar Generation Script

Thanks,

Pooja

[balabhaskarqlik]

And, You've to consider Fiscal Year Start Month also, By that again need to do few more modifications on Master calendar scripting.

[balabhaskarqlik]

Check this:

Fiscal and Standard Calendar generation

[balabhaskarqlik]

This too:

Master Calendar for Fiscal Year starting April

[My_Rebecca]

Dear @Anonymous , I have listed the calendar, but still have the follwoing error. How to improve it?

Count(distinct(GI_Delivery & [GI_Delivery Item]))/(7*8*(NetWorkDays('$(=vProductivity_MinDate)','$(=vProductivity_MaxDate)')

The total productivity (1.6) are divided by FY22 & FY23 (1.1+0.5).  Obviously this is wrong. The correct result should be almost equal to 1.6 no matter which year, quarter, month are filtered.