Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
jonesbrown
Creator
Creator

How to Create Master Calendar ?

Hi All,

I am new to Qlikview

How to Create Master Calendar ?

Please Help.

3 Solutions

Accepted Solutions
Gopi_E
Creator II
Creator II

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.

View solution in original post

saratchandra30
Contributor III
Contributor III

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

View solution in original post

pooja_prabhu_n
Creator III
Creator III

8 Replies
m_woolf
Master II
Master II

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
Creator II
Creator II

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

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

saratchandra30
Contributor III
Contributor III

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
Creator III
Creator III

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

balabhaskarqlik