Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

13 Replies
hic
Former Employee
Former Employee

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

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

HIC

richard
Partner - Creator
Partner - Creator

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,

Not applicable
Author

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

hic
Former Employee
Former Employee

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