How to create a Calendar in QlikView 9?
UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/
Ok, so the fact that I cannot just have one line in a Load Script is a negative for QlikView. In a perfect world, I would have one line that would give me a bunch of possible values I could use for a dimension, such as CalendarDay, CalendarWeek, CalendarMonth, CalendarQuarter, CalendarYear, etc… It would be one line like this:
Alas…it is not a perfect world, so this feature doesn’t exist in QlikView. (Enhancement Request please!!!!)
So there is a Wiki on how to do it. Here is the link.
However, the problem is that this didn’t work.
So after some research I remember that internet search engines exist and I don’t have to just search QlikView’s site and documentation. I did a google search for this string:
qlikview how to create a calendar
The script didn’t fail to load…yeah…wait…there is not data in my report that has to do with a Calendar.
This sucks. Why can’t I just create a new Calendar. This is common problem with some software companies. There is a “key features” that can be done, but with great difficulty. However, because it can be done, they don’t spend any more development time on it.
Here is the result:
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());
$(vDateMin) + RowNo() – 1 AS DateNumber,
Date($(vDateMin) + RowNo() – 1) AS TempDate
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()); [/sourcecode] Now when your script loads, you can right click and choose New Sheet Object, Slider/Calendar Object. Choose Calender, not Slider and base it off of the CalendarDate field. Also on the Sort tab, use the Numeric Value to change the sort to Descending.