
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Master Calendar Question
I was studied the Master Calendar function last night. I completely understand the logic and what the script creates. I created a new application with the following information only:
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
MasterCalendar:
LOAD
TempDate AS CalendarDate,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());
From there a created a list box for each of the date data. Now what I need help on is a couple good business reasons why I would use the Master Calendar. I know I can search and find examples but I want to see what the members have done. Any ideas would be awesome.
- Tags:
- qlikview_scripting
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You need to add the fiscal year, month and other date related fields to the MaterCalender table.
Your offset is your TempDate field.
Example:
//Fiscaldata
year(AddMonths(TempDate,3)) AS Fis_Year,
'Q' & ceil(month(AddMonths(TempDate,3))/3) AS Fis_Quarter,
num(month(Addmonths(TempDate,3))) AS Fis_Period,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Master Calendars are mainly created and used for two good reasons (but those aren't necessarily business reasons, it's more to do with usability I think):
- use a Master Calendar if you don't want timelines with holes in them. If you just use the order date field to base your timeline upon, dates on which no orders were placed won't be there... Master Calendar = continuous timeline
- use a Master Calendar (or a Link Table with dates and additional dimension fields) if you want to connect multiple facts tables that have differing date granularity (for example Actuals = day or weekly, budget = monthly) and neither one of them may filter the available dates/periods.
Of course, YMMV.
Peter

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As soon as you have a date in your data, it makes sense to load a Master Calendar. You don't want to clutter your fact table with a bunch of calendar fields like Week and Month.
A comment on your script: You do not need a resident load. Instead you should use a preceding Load. It's both cleaner and faster. E.g.
LOAD Date AS CalendarDate,
Day(Date) AS CalendarDay,
WeekDay(Date) AS CalendarWeekDay,
* ;
LOAD Date($(vDateMin) + RecNo() - 1) AS Date
AUTOGENERATE $(vDateMax) - $(vDateMin) + 1;
HIC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Like HIC says - Then you dont need to fill your Fact tables with Week,Month,Year and so on.
But many BI consultants also create a Date Dimension in their date warehouse which includes,Year,Month,Quarter and so on.
Normally I tend to use both, because I can use the MasterCalendar as a template across all document i might develop.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you. That makes sense.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Lets say, we have the Fact table with 100K rows from Last one Year history.
Generally we have only 365 Date fields in one year. If you don't have calender table, you run the Year , Month, Week and some flags (YTD) on 100K rows and it take longer reloads time.
If you have the small table having 365 rows and created all the Year, Month, Week & other flags in this table, it take very less time to create the Date dimensions.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you. So here is my problem:
I have two dates one in the AcctSetup table and One in the Bill Table:
- FiscalYearBeginDate which could be 7/1/2012, 8/1/2012, 9/1/2012, etc. (AcctSetup Table)
- BillStartDate which could be any date so as an example 2/1/2013 (Bill Table)
I load my AcctSetup Table:
AcctNum
FiscalYearBeginDate
......
I load my Bill Table
AcctNum
BillStartDate
....
What I need to know is the following:
If FiscalYearBeginDate = 7/1/2012 then BillDate 2/1/2013 is Month 7 of Fiscal Year 2012, 8/1/2012 is Month 6 of Fiscal Year 2012, 9/1/2012 is Month 5 of Fiscal Year 2012, etc.
So for each BillStartDate I need to know which month and fiscal year that date falls into.
The AcctSetup Table will look like:
AcctNum FiscalYearBeginDate
123 7/1/2012
456 8/1/2012
The Bill Table will look like:
AcctNum BillStartDate So my results need to be
123 2/1/2013 Month = 7 and Fiscal year = 2012
456 2/1/2013 Month = 6 and Fiscal year = 2012
Does this make sense?
David

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Henric,
Thank you for your suggestion. I am still learning about Master Calendars and so forth. It sounds to me whenever you have a date field in a table it might be good to create a Master Calendar. I am going to continue to experiment with them. Let me ask you a question
I have a table with CreateDate and ChangeDate loaded. I need a two charts one to show how items were created based on the CreateDate field. I need another Chart to show how many items changed based on the ChangeDate. The charts will be independent of each other so what I want is two list boxes. One with the Months(Jan, Fed, etc) and the other Years(2011,2012,etc). Can you make it so if a person selects a Year the chart shows how many items were created and the other chart shows how many items were changed? If some selects a Month they get the same type of results and finally if they select both they get the same type of results. Does this make sense? I am guessing one calendar but have to combine the two date fields together.
David

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes you can. You need to create a common date to represent both dates. This you do in the script by the following two Load statements:
GenericDate:
Load TransactionID, CreateDate as Date, 'Create' as DateType resident Facts;
Load TransactionID, ChangeDate as Date, 'Change' as DateType resident Facts;
Then you connect your master calendar to this new date. You need to use Set Analysis in the charts. The reason is that a item will link to two dates - it will be counted twice unless you use Set Analysis. Hence:
Sum({$<DateType={'Create'}>} Amount)
Count({$<DateType={'Create'}>} ItemID)
HIC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi HIC,
Sorry to be asking so many questions. I still have to create a Master Calendar along with the information you provided in the email above correct. I don't know why I am so confused with the Master Calendar concept but for some reason I am. I want to make sure I use the best code when building a Master Calendar and would love to have and example of the load statement.
David

- « Previous Replies
-
- 1
- 2
- Next Replies »