Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This videos show how to create a Master Date Calendar in QlikView. The script mentioned in the video is below.
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;
Thanks! For anyone looking to learn the fundamentals of using a master calendar, I would definitely recommend watching this video.
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 <=
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).
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
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
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
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
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;