topic Re: How to Create Master Calendar ? in QlikView

How to Create Master Calendar ?

jonesbrown — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am new to Qlikview

How to Create Master Calendar ?

Please Help.

Re: How to Create Master Calendar ?

m_woolf — Mon, 20 Aug 2018 15:16:47 GMT

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');

Re: How to Create Master Calendar ?

Gopi_E — Mon, 20 Aug 2018 15:22:56 GMT

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.

Re: How to Create Master Calendar ?

jmmolero — Mon, 20 Aug 2018 15:24:06 GMT

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

Re: How to Create Master Calendar ?

Anonymous — Tue, 21 Aug 2018 01:59:48 GMT

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

Re: How to Create Master Calendar ?

pooja_prabhu_n — Tue, 21 Aug 2018 10:22:30 GMT

Hi,

refer this link

Master Calendar Generation Script

Thanks,

Pooja

Re: How to Create Master Calendar ?

BalaBhaskar_Qlik — Tue, 21 Aug 2018 14:45:55 GMT

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

Re: How to Create Master Calendar ?

BalaBhaskar_Qlik — Tue, 21 Aug 2018 14:48:19 GMT

Check this:

Fiscal and Standard Calendar generation

Re: How to Create Master Calendar ?

BalaBhaskar_Qlik — Tue, 21 Aug 2018 14:48:38 GMT

This too:

Master Calendar for Fiscal Year starting April

Re: How to Create Master Calendar ?

My_Rebecca — Mon, 10 Apr 2023 05:35:14 GMT

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.

Re: How to Create Master Calendar ?

Hania — Mon, 10 Apr 2023 06:04:16 GMT

Hii

//-------------------------------------------1 step-------------------------------------------------------//
// create table and extract min and max date
// define Min Max Date
min_max:
Load

Min(Delivery_Date) as minDate,
Max(Delivery_Date) as maxDate

Resident SALESDETAILS;

//-------------------------------------------2 step-------------------------------------------------------//

// create variable and peek dates of min max from loaded table (min_max)
Let vMinDate = Peek('minDate',0,'min_max');

Let vMaxDate = Peek('maxDate',0,'min_max');

//after fetching min max dates from min_max --> no use of it so drop table
drop table min_max;

//-------------------------------------------3 step-------------------------------------------------------//

// creating master calendar & Extract all the necessary date columns required for the dashboards between min and max
Master_Calendar:
Load

MonthName(Delivery_Date) as MonthName,
QuarterName(Delivery_Date) as QuarterName,
YearName(Delivery_Date) as YearName,
'Q'&Ceil(Month(Delivery_Date)/3) as Quarter,
Year(Delivery_Date) as Year,
Month(Delivery_Date) as Month,
WeekDay(Delivery_Date) as WeekDay,
Week(Delivery_Date) as Week,
Day(Delivery_Date) as Day,
Year(Delivery_Date) as yearDate,
'HY'&Ceil(Month(Delivery_Date)/6) as HalfYear

;
//-------------------------------------------4 step-------------------------------------------------------//

//looping--> from minimum date till maximum date rech
Load

Date($(vMinDate) + IterNo() - 1) as Delivery_Date

AutoGenerate 1

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

Hope this helps,

help user find answers ! don't forget to mark a solution that work for you and click the like button!

Re: How to Create Master Calendar ?

My_Rebecca — Mon, 10 Apr 2023 10:56:41 GMT

No matter how, I do appreciate your reply. I would check and mark as solution if feasible.

Re: How to Create Master Calendar ?

My_Rebecca — Thu, 13 Apr 2023 05:59:31 GMT

Dear @Hania , do your script support cross-period selection? For example, if I filter January and March, could this expression result is calculated from the total days of January & March (62 days), not from the beginning of January to March (90 days)? From your script, I do not see anything special about this point. As I have tried your similar method before, it does not work.

My expression:

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

Re: How to Create Master Calendar ?

Hania — Thu, 13 Apr 2023 06:33:57 GMT

Hi,

You should add Filter for month & date picker as well based on your requirement so you can select your desirable months and if you want to view data based on specific Date/Random date you can visualize with ease.

As you can see it work based on selection.

Re: How to Create Master Calendar ?

My_Rebecca — Fri, 14 Apr 2023 07:36:22 GMT

Dear @Hania , thanks for your reply. Easy selecting month and date is not the problem, but expression is not easy to reach. please focus on my expression and look forward to your reply.

For example, if I filter January and March, could this expression result is calculated from the total days of January & March (62 days), not from the beginning of January to March (90 days)? From your script, I do not see anything special about this point. As I have tried your similar method before, it does not work.

My expression:

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

Re: How to Create Master Calendar ?

ss2q — Fri, 01 Mar 2024 13:38:07 GMT

How to create a master calendar and link it if I have two tables?

One is the Order table which contains Ordered_Date and another is the Invoice table which contains GL_date. From these date columns, I extract the Year from each of the tables. Like from the order table, I extract the year from the order date and in the invoice table I extract the year from gl_date. Now I want to create a master calendar by linking the year from both tables and using the master calendar Year in my analysis. Is it possible? What will be the code if someone can tell me and How can I relate to the data manager or something?

Re: How to Create Master Calendar ?

Ravi_Nagmal — Fri, 01 Mar 2024 13:53:36 GMT

@jonesbrown This might help.

https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578

Re: How to Create Master Calendar ?

ss2q — Sat, 02 Mar 2024 15:25:54 GMT

How about if I want make like this?

Re: How to Create Master Calendar ?

Hania — Wed, 06 Mar 2024 11:54:26 GMT

@ss2q In that case you can use Link Table .