How to create a Calendar in QlikView 9?

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:

Calendar(StartDate, EndDate);

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.
http://community.qlikview.com/wikis/qlikview-wiki/how-to-create-a-calendar.aspx

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.

Anyway, I added a post in the QlikView Forum and watched the QlikView free training Video for developers (especially module 8).

Here is the result:

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()); [/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.

9 Comments

  1. www.masanri.wo.tc

    How to create a Calendar in QlikView 9? | Rhyous

  2. jiqingmengnv.com

    How to create a Calendar in QlikView 9? | Rhyous

  3. please click the next web page

    How to create a Calendar in QlikView 9? | Rhyous

  4. Varsha says:

    Great.. Thanks for making it so simple 🙂

  5. Great website. Lots of useful information here. I'm sending it to a few pals ans additionally sharing in delicious. And of course, thanks to your effort!

  6. Márcio says:

    How do I create a new sheet with script?

Leave a Reply

How to post code in comments?