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: 
Josh_Good
Employee
Employee

Creating A Master Calendar

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;

78 Replies
Not applicable

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

Not applicable

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
Employee
Employee
Author

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).

2012-05-24_0014.png

Not applicable

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
Employee
Employee
Author

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
Creator
Creator

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Employee
Employee
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

How about:

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

-Rob

http://robwunderlich.com