topic Re: Use of Master Calendar in QlikView

Use of Master Calendar

sunny_talwar — Wed, 01 Apr 2015 17:30:22 GMT

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

Re: Use of Master Calendar

Anonymous — Wed, 01 Apr 2015 17:40:24 GMT

Great question...I wonder the same thing whenever I create master calendar. In fact when we don't create master calendar and keep date, month, years etc. in the fact table itself, the method will be less CPU intensive. I am curious to see expert's take on this issue.

Re: Use of Master Calendar

MK_QSL — Thu, 02 Apr 2015 07:57:26 GMT

Sunny... Excellent Question... There could be different answers on advantages/disadvantages on creating/use of Master Calendar. To create and use Master Calendar is completely depends on what type of data model and domain it is and what are the requirements.

Consider that you have Sales table where you have all Invoice related information and Customer ID. We usually prefer to associate Sales Table with Customer tables via Customer ID to get all customer related information rather than keeping Customer information in Sales table to avoid duplication of data. (i.e. One Invoice belongs to a single customer could have 10 lines, so all information of Customers would get duplicated)... The same way I would prefer to create separate calender from InvoiceDate field to get Year, Month, Quarter, Week, MonthYear, YearMonth informations.

Sometimes client want to show 0 sales value when you don't have sales on particular day (usually happens on weekends or holidays). To show 0 sales on Bar Chart, what we want to create those missing dates, so I think Master Calendar is the best and easiest option in this case.

How you will show Daily Sales Growth compare to previous day if you don't have data for previous day? You first need to create those missing Dates ! There are also alternative way to get the result but Master Calendar could be the easier one.

Hope we would have more replies from some experts !

Happy Qliking !

EDIT 02/04/2015 (09:30 UTC)

Completely forgot to mention the use of Canonical Date. Without creating a separate calendar/master calendar, it's very difficult to show data for table having two different date fields.

Order Table having OrderDate and Delivery Date.

If one want to show Daily/Monthly/Weekly/Quarterly/Yearly number of Orders received vs Number of Orders Delivered...or Amount instead of Order Count... we need to create a Common Calendar with Flag Order and Delivered...

Re: Use of Master Calendar

sunny_talwar — Thu, 02 Apr 2015 09:00:45 GMT

Hi Manish,

Thank you for taking out some time to responding back to this post. I really appreciate you listing out advantages of having a master calendar and I am almost tempted to mark your answer as correct, but I just want to wait a little longer before I close this thread.

Coming back to the three advantages you listed, I feel that 2 and 3 seems to be the true advantage of having the master calendar. Like you said that comparison with a previous day would make your life so much harder without a master calendar. and whenever we want to show sales for days where we did not have any sales, it wouldn't be possible without a master calendar.

But to your first 1st point relating to duplication of information, it makes sense for client, employee, supplier, manufacture where each of them will be linked to just one address phone no. etc. In case of master calendar we will still have the same duplication with even more set of dates. For example if I do all the manipulations in the fact table it would be in just one table and number of rows will be less

Transaction ID	Date	Month	Year
100	02/02/2015	Feb	2015
101	02/04/2015	Feb	2015

but if I do the same in the master calendar without left keep, I will have something like this:

Date	Month	Year
02/02/2015	Feb	2015
02/03/2015	Feb	2015
02/04/2015	Feb	2015

I would think that if you don't have a requirement like the 2nd or 3rd point you mentioned, it would make more sense to do the date field manipulations in the fact table itself or if you really wish to have a separate table you can always create it like this (instead of regular method)

MasterCalendar:

LOAD Date,

Year(Date) as Year,

Month(Date) as Month,

and so on...;

LOAD DISTINCT Date

FROM Fact;

Once again, I feel you introduced some great reasons to have a Master Calendar and I really want to thank you for your time and effort to clarify things.

I hope that other experts will also contribute to this thread.

Best,

Sunny

Re: Use of Master Calendar

MK_QSL — Thu, 02 Apr 2015 09:22:26 GMT

Regarding my 1st Point...

What if you have One invoice having 10 lines of data.

InvoiceDate, InvoiceNumber, LineNo, Item, Price, Qty

01/01/2014, INV001, 1, A1, 100, 10

01/01/2014, INV001, 2, A2, 110, 12

01/01/2014, INV001, 3, A3, 120, 9

01/01/2014, INV001, 4, A4, 10, 8

and so on..

If you will create Month, Quarter, Week, Year field in the same table, there would be duplication of data in each line but in Master Calendar particular for 01/01/2014, data will come for once only....

I also want not to close this thread so that other experts like below would also comment..

Kind request to all of you to share your thoughts on this...

Your reply would be highly appreciated... Thanks !

Re: Use of Master Calendar

sunny_talwar — Thu, 02 Apr 2015 09:37:41 GMT

Yes that definitely makes sense. I did not think of that. I guess in that case it would make sense because Fact table could have millions of rows of data vs Master Calendar which would be only 10's of thousand.

Re: Use of Master Calendar

marcus_sommer — Thu, 02 Apr 2015 11:45:29 GMT

We had had a quite similar discussion in my session (User Group Session "Re-Using von Scripten, Formeln und Daten") by the central usergroup-meeting DACH last year in beforehand from the VYWK in Frankfurt and discussed this topic with swr. In short, the result was that the use from a master-calendar is recommended then there are a lot of advantages:

Advantages from a master-calendar against creating period-fields within the fact-tables are:

- structure of data-model (facts linked to dimensions)

- less RAM will be needed (amount of fields and distinct values will be the same (unless date-holes) but the efforts for the pointer will be greater by large fact-tables)

- no holes within the dates

- it's easier to create "extended" date-fields like continuing month- or week-counter or counting the working-days and so on

- using the same master-calendar for all applications (maybe with where-clauses over minDate/maxDate from facts or rolling years or fixed data-ranges)

- using the master-calendar several times for a single application (canonical date)

- Marcus

Re: Use of Master Calendar

sunny_talwar — Thu, 02 Apr 2015 12:05:48 GMT

Hi Marcus,

Thanks for your response. I am unclear about some of the advantages you listed here, would you be able to elaborate on these a little:

- less RAM will be needed (amount of fields and distinct values will be the same (unless date-holes) but the efforts for the pointer will be greater by large fact-tables) - Do you mean if we create all dates regardless of if there is a fact available in the fact table vs. doing it in a master calendar, the RAM consumption will be more if its within the fact table??

- it's easier to create "extended" date-fields like continuing month- or week-counter or counting the working-days and so on - what is "extended" date field (I know you mentioned continuing month- or week-counter, does it literally means assigning a some kind of flag for a required duration?)

- using the same master-calendar for all applications (maybe with where-clauses over minDate/maxDate from facts or rolling years or fixed data-ranges) - Don't understand the part in brackets (why do we need a where clause when we use it in other application, may be I don't understand the context)

Once again I really appreciate your time and effort on helping me understand this better. I am sure this will prove helpful for not just me, but many other in the community as well.

Best,

Sunny

Re: Use of Master Calendar

marcus_sommer — Thu, 02 Apr 2015 12:44:44 GMT

To the point RAM - exampl: if you create to your date two further fields month and year you will have two additional fields with nearby the same number of distinct values if create these fields within the fact-table or you linked it per master-calendar. But these fields needs within large fact-tables more pointer to link they to the date. It's not a easy topic which I could explain well therefore have a look here Symbol Tables and Bit-Stuffed Pointers and in the book Mastering QlikView from stephen.redmond.

To "extended" date-fields: Yes, with them are flags, pre-calculations and simplifications meant. MonthCounter for examples is a continuning month-number, started with 1 and after a year it will be increased from 12 to 13 and so on. Use is the simplification of expression which query date-ranges over years - sum{< Monthcounter = {">=$(=max(Monthcounter) - 6)"}>} Value).

To where-clauses: It was meant to use one master-calendar for all applications but not each application need to cover the complete data-range from the master-calendar. Mine covered more than two decades but the most applications included only a few years.

- Marcus

Re: Use of Master Calendar

sunny_talwar — Thu, 02 Apr 2015 12:50:32 GMT

Thanks Marcus for providing clarification. Your help on the topic is greatly appreciated.

Best,

Sunny

Re: Use of Master Calendar

Anonymous — Thu, 02 Apr 2015 13:12:37 GMT

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.

Re: Use of Master Calendar

sunny_talwar — Thu, 02 Apr 2015 13:33:30 GMT

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

Re: Use of Master Calendar

Anonymous — Thu, 02 Apr 2015 13:44:52 GMT

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)

Re: Use of Master Calendar

sunny_talwar — Thu, 02 Apr 2015 13:47:06 GMT

Awesome!

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

Best,

Sunny

Re: Use of Master Calendar

sunny_talwar — Thu, 02 Apr 2015 13:52:16 GMT

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

Re: Use of Master Calendar

Anonymous — Thu, 02 Apr 2015 17:56:14 GMT

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

Re: Use of Master Calendar

stevedark — Fri, 03 Apr 2015 20:31:34 GMT

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

Re: Use of Master Calendar

Thu, 12 Jan 2017 05:45:15 GMT

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 !

Re: Use of Master Calendar

veerendrakatika — Tue, 23 May 2017 14:26:20 GMT

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

Re: Use of Master Calendar

ajaysikharam — Wed, 09 Aug 2017 16:35:28 GMT

valuable inputs