Use of Master Calendar

[sunny_talwar]

Hi Qlikers,

I have a question related to the actual benefit of having a Master Calendar. The discussion came about because one of my colleague recently created a Master Calendar not based on date, but based on a TimeStamp field. And after creating the calendar, he did a left keep to drop of the new timestamps he just created. I argued with him that what is the point of creating an extra table when he is going to do a left keep. He can do the same within the fact table. To this he argued that all the date, month, year calculations can be separated out in a different table (makes sense), but aren't we taking extra steps for something we can do it within the fact table?

Real Question: I have been using master calendar for a while and I don't use left keep, but what is the benefit of creating a master calendar? We don't even use the dates which are not available in our fact table, do we?? Is there a hidden benefit that we are missing by having all dates in our application.

All responses are greatly appreciated.

Best,

Sunny

[Anonymous · Accepted solution]

Agree with MRKachhiaIMP 100%.  Unless you have a one-table "data model" and you know for sure that you'll never use date as dimension in charts, you have nothing to loose using Master Calendar.  To repeat the advantages:

1. Have the full list of dates in the Calendar range.  Speaking of range (and going back to your "left keep" - typically we create Calendar before loading Facts data, and when loading Facts we use Calendar range as condition, hence filtering out the facts outside the required range.

2. Avoid multiplication of the same data in Fact table.

3. Using the same Calendar for many Fact dates.

[sunny_talwar]

Hi msolomov ,

Thanks for your response.

I have been usually creating the master calendar using the max and min date from the fact table. I thought that was the way to do it. But you brought another interesting thing forward: To create the master calendar before the fact table. So far this is what I have been doing:

Temp: //Using the fact to extract min and max date

LOAD Min(Date) as minDate,

          Today() as maxDate

Resident FactTable;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

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

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

AutoGenerate

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

MasterCalendar:

LOAD

  TempDate as Date,

  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,

  'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,

  MonthName(TempDate) as MonthYear,

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

Resident TempCalendar

Order By TempDate ASC;

DROP Table TempCalendar;

This might be whole another topic (and I am cool with starting another discussion for that), but is there a benefit of bringing the master calendar before the fact table?

Best,

Sunny

[Anonymous]

This is as simple as that:

- If you are interested in the data only in the specific time range (e.g. 2 years back and one year forward), start with the calendar.

- If you need all the existing facts data (for me it's quite rare), create calendar after loading facts.

Besides, I don't like calendars that start or end with something like March 13 .  So I typically make start date as the start of the year, and end date as end of the year.  In your example it would be:

LET vMinDate = Floor(YearStart(Peek('minDate', 0, 'Temp')));

LET vMaxDate = Floor(YearEnd(Peek('maxDate', 0, 'Temp')));

(Or at least month start/end)

[sunny_talwar]

Awesome!

Thanks Michael for sharing your good knowledge with me and all other.

Best,

Sunny

[sunny_talwar]

Thanks Manish‌,  Marcus_Sommer‌,  msolomov‌ for sharing your knowledge and expertise. From the above discussion, I draw the conclusion that more often then not, it is useful to have a master calendar in your data structure. All your inputs are valuable which makes it hard for me to decide whose answer should I mark as correct. I think I am going to randomly assign one of the answers as correct.

Once again thanks for your time and valuable insights.

Best,

Sunny

[Anonymous]

Thanks everybody for sharing your deep insight. I highly appreciate your efforts.

[stevedark]

I put some of my thoughts on Master Calendar in this post:

http://www.quickintelligence.co.uk/qlikview-calendar/

I tend to incorporate date break downs into the main fact table, rather than hanging a calendar off the side.  There are advantages and disadvantages of this approach.

The article looks at some of these, and there is a fair amount of conversation about the topic in the comments after.

Enjoy!

Steve

[#9]

Hello Sunny,

Sorry for posting this link on your post but its really urgent please do have a look towards my one big problem.

I want to get Avg of a Ticket on every day basis.

I hope you will be the one who can help me out with the same.

Urgent Help !

[veerendrakatika]

Thanks Manish for sharing your valuable suggestions on the master calendar. 

[ajaysikharam]

valuable inputs