November 20, 2009

How to create a Calendar in QlikView 9?

Filed under: QlikView,Reporting — J. Abram barneck @ 3:35 pm

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.

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());

  $(vDateMin) + RowNo() - 1 AS DateNumber,
  Date($(vDateMin) + RowNo() - 1) AS TempDate
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
	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());

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.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: