topic Re: Master Calendar Question in QlikView

Master Calendar Question

Thu, 09 Jan 2014 13:26:44 GMT

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.

Re: Master Calendar Question

Peter_Cammaert — Thu, 09 Jan 2014 14:04:08 GMT

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

Re: Master Calendar Question

hic — Thu, 09 Jan 2014 14:29:48 GMT

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

Re: Master Calendar Question

Thu, 09 Jan 2014 14:39:18 GMT

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.

Re: Master Calendar Question

Thu, 09 Jan 2014 14:52:39 GMT

Thank you. That makes sense.

Re: Master Calendar Question

Thu, 09 Jan 2014 15:34:51 GMT

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.

Re: Master Calendar Question

Thu, 09 Jan 2014 16:07:28 GMT

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

Re: Master Calendar Question

Mon, 13 Jan 2014 23:42:01 GMT

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

Re: Master Calendar Question

hic — Tue, 14 Jan 2014 07:08:50 GMT

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

Re: Master Calendar Question

Tue, 14 Jan 2014 13:16:39 GMT

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

Re: Master Calendar Question

hic — Tue, 14 Jan 2014 14:32:17 GMT

Your above script is very close to what I would do. However, I would suggest two changes:

Use a preceding load instead of a temporary table
Define rolling months using Date(MonthStart(Date),'YYYY-MMM') as CalendarMonthAndYear

HIC

Re: Master Calendar Question

richard — Tue, 14 Jan 2014 15:30:37 GMT

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,

Re: Master Calendar Question

Tue, 21 Jan 2014 15:57:39 GMT

Thank you Henric. You have helped me out with questions I had on Master Calendar before. I have it in different responses and want to put it all together. I was wondering if you could help me out providing me an example that combines all the steps together when creating a Master Calendar. The best way, variables, etc. I am going to give you the scenario and if you can fill in the gaps that would be awesome. I have included some comments about the field in bold but they are not in script.

GOAL: To have three charts. One for each of the Date Fields from my Load Statement. The charts would be how many new IP's were created, how many IP's had their first training session, and how many IP's did we have go live. We might have 10 IP's created, 15 IP's had their first training session, and 4 IP's went live. The 15 IP's is strictly looking at the "Initial_Training_Scheduled_For__c" so it is capturing the actual number of trained IP's for that month regardless of when those IP's we created.

I hope this makes sense.

Here is my Load Statement:

Accounts:

LOAD Id as AccountId,

Name as OrganizationName,

Industry

FROM

Account.qvd

(qvd);

ImplementationPlans:

LOAD Id as IPID,

CreatedDate, //This is the date the IP was Created//

Initial_Training_Scheduled_For__c, //This is the date the IP was first Trained//

Go_Live_Date__c //This is the date the IP was Live//

FROM

Implementation_Plan.qvd

(qvd);

MasterCalendarIPCreate:

LET vDateMin = Num(MakeDate(2012,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 CreatedDate,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

MasterCalendarIPCreate:

LET vDateMin = Num(MakeDate(2012,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 Initial_Training_Scheduled_For__c,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

MasterCalendarIPLive:

LET vDateMin = Num(MakeDate(2012,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 Go_Live_Date__c,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

I believe I have correct syntax for the above Load Statement. I have included a copy of my Dashboard view to help you out.

1. I created a ListBox for Industry ( I used the Industry Field)

2. I created a ListBox for Years (I used the CalendarYear Field)

3. I created a ListBox for Months (I used the CalendarMonth Field)

4. The charts are for the three date fields I want to return results on:

a. Total IP's Created

b. Total IP's First Training

c. Total IP's Live

This is where I am still a little confused. How do I show results based on The Year, The Month, or Both depending on what options are selected? My guess is through the Dimension and the Expression on each of the charts but not sure what it would look like. I have tried different ones and must be using the wrong fields or calculations. Can you give me some insight?

Thanks

David

Re: Master Calendar Question

hic — Wed, 22 Jan 2014 22:53:58 GMT

I think you make it too complicated. Your three calendars link to each other, and you probably don't want that.

There are two different solutions to this problem:

1) Create three different Master Calendars. Each field must have a unique name, so no field is named as in the other calendars.

2) Create one common, generic date for all three dates.

You can implement both solutions in the same app.

====== Solution 1: ======

Don't use temp tables. Use preceding load instead:

GoLiveCalendar:

LOAD

TempDate AS Go_Live_Date__c,

Month(TempDate) AS Go_Live_Month,

Year(TempDate) AS Go_Live_Year,

Date(MonthStart(TempDate), 'MMM-YYYY') AS Go_Live_MonthAndYear

;

LOAD Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Repeat this code for "CreatedDate" and for "Initial_Training_Date".

With solution 1 you can select a specific creation month and see when training and go live occurs.

====== Solution 2: ======

Each Impementation Plan ("IPID") must link to 3 different dates, all stored in the same "Date" field. Hence:

IP_Bridge:

Load IPID, CreatedDate as Date, 'Created' as DateType Resident ImplementationPlans;

Load IPID, Initial_Training_Scheduled_For__c as Date, 'Training' as DateType Resident ImplementationPlans;

Load IPID, Go_Live_Date__c as Date, 'GoLive' as DateType Resident ImplementationPlans;GoLiveCalendar:

GenericDate:

LOAD

TempDate AS Date,

Month(TempDate) AS Month,

Year(TempDate) AS Go_Year,

Date(MonthStart(TempDate), 'MMM-YYYY') AS MonthAndYear

;

LOAD Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

With solution 2 you can use one single Date as X-axis in a chart, and use expressions like

Count({$<DateType={'Created'}>} distinct IPID)

Count({$<DateType={'GoLive'}>} distinct IPID)

to count number of IPs created and number of IPs that go live.

HIC