Creating A Master Calendar

[Josh_Good]

This videos show how to create a Master Date Calendar in QlikView.  The script mentioned in the video is below.

http://youtu.be/ScdIQvWzVFs

QuartersMap:
MAPPING LOAD 
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
               min(OrderDate) as minDate,
               max(OrderDate) as maxDate
Resident Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
               TempDate AS OrderDate,
               week(TempDate) As Week,
               Year(TempDate) As Year,
               Month(TempDate) As Month,
               Day(TempDate) As Day,
               YeartoDate(TempDate)*-1 as CurYTDFlag,
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

[#2]

Thanks! For anyone looking to learn the fundamentals of using a master calendar, I would definitely recommend watching this video.

[#3]

Josh, I've tried to implement the Master Calendar after having watched your video, and I feel like I understand how everything works, but I'm getting an error on reload with the temp table/autogenerate. Any idea why that would be?

Here is my error:

Field not found - <<=>

TempCalendar:

LOAD

                + Iterno()-1 As Num,

               Date( + IterNo() - 1) as TempDate

               AutoGenerate 1 While  + IterNo() -1 <=

[Josh_Good]

It looks like you are not generating values for varMinDate (and like also for varMaxDate) for some reason.  Try using the degubber and running the script using 'Step' to confirm this (see image which show what you should be seeing if you are generating values).  The reason for this could either be your Temp table is empty or there is some sort of syntax error on the line that sets the variable varMinDate (line 37 of the image below).

[#5]

That's what I thought too Josh, so here is what I did.

To try and debug, I removed everything but the below:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
               min(PerformanceDate) as minDate,
               max(PerformanceDate) as maxDate
Resident DimPerformanceDate;

When I run the above, and display mindate and maxdate in a listbox it definitely contains values.

But once it gets to that autogenerate line it can't find them...

Also, another error I see in debugger is that monthstart must contain two values. (Not sure)

Does your script only work on a certain version? Because these problems are really strange.

I'm running QV9. Figured I'd throw that out there before I keep trying to troubleshoot something that may not work.

Thanks for your response,

-Anthony

[Josh_Good]

Hi Anthony,

Yes the script should work in V9.  I origionally started using in V9 myself.  I'm a bit at a lost as to why you are running into an error.  The only thing I can think of is that your data is not being recongized as dates by QlikView.  Maybe see if you can get the script to work with different data (to rule that out).

Regards,

Josh

[userid128223]

Hi Josh

Your master calander calculates quarter

q1 = jan to march

q2= apr to jun

what if the quarter starts from dec.

q1=dec - feb

q2= mar - may

how do you make changes to master calander to accomodate that.

please help

thanks

[rwunderlich]

We are all coding MasterCalendars over and over again. It's an interesting excercise to learn scripting, but if you just want to get the calendar done, consider using Qlikview Components http://qlikviewcomponents.org, the free open source QV script library.

http://qlikviewnotes.blogspot.com/2012/01/easy-period-analysis-using-qlikview.html

-Rob

[Josh_Good]

Hi,

To do this you will need to change the Mapping table to match your quarters.  I would either drive it off fiscal year definitions from a data source or using an inline load. The inline load would look like this:

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

2, Q1

3, Q2

4, Q2

5, Q2

6, Q3

7, Q3

8, Q3

9, Q4

10, Q4

11, Q4

12, Q1

];

You may also want to define a Fiscal Year as well.  In the load statement that defined the Master Calendar table you could add a line something like this:

If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

Putting it all together your script would look something like below.

I hope that helps!

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

2, Q1

3, Q2

4, Q2

5, Q2

6, Q3

7, Q3

8, Q3

9, Q4

10, Q4

11, Q4

12, Q1

];

Temp:

Load

               min(OrderDate) as minDate,

               max(OrderDate) as maxDate

Resident Orders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

               TempDate AS OrderDate,

               week(TempDate) As Week,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

               Week(TempDate) & '-' & Year(TempDate) as WeekYear,

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

[rwunderlich]

How about:

'Q' & Ceil (month(AddMonths(Tempdate,1)/3) as Quarter

-Rob

http://robwunderlich.com