Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
mov
Esteemed Contributor III

Re: Use of Master Calendar

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.

View solution in original post

Highlighted

Re: Use of Master Calendar

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

Highlighted
mov
Esteemed Contributor III

Re: Use of Master Calendar

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)

Highlighted

Re: Use of Master Calendar

Awesome!

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

Best,

Sunny

Highlighted

Re: Use of Master Calendar

Thanks Manish‌,  Marcus_Sommer‌,  msolomovfor 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

Highlighted
qlikrajan
Contributor III

Re: Use of Master Calendar

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

MVP & Luminary
MVP & Luminary

Re: Use of Master Calendar

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

Highlighted
Not applicable

Re: Use of Master Calendar

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 !

Highlighted
veerendrakatika
New Contributor III

Re: Use of Master Calendar

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

Highlighted
ajaysikharam
New Contributor

Re: Use of Master Calendar

valuable inputs