Rhyous

November 30, 2009

My new and improved Calendar in QlikView 9

Filed under: QlikView — J. Abram barneck @ 1:33 pm

Hey all,

After working with QlikView for a few days, and working with the calendar, here is my new and improved load script for a Calendar.

Calendar:
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(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,

	// Standard Date Objects
	Day(TempDate) AS CalendarDay,
	WeekDay(TempDate) AS CalendarWeekDay,
	Week(TempDate) AS CalendarWeek,
	Month(TempDate) AS CalendarMonth,
	'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
	Year(TempDate) AS CalendarYear,
	
	// Calendar Date Names
	DayName(TempDate) as CalendarDayName,
	WeekName(TempDate) as CalendarWeekName,
	MonthName(TempDate) as CalendarMonthName,
	QuarterName(TempDate) as CalendarQuarterName,
	YearName(TempDate) as CalendarYearName,
	
	// Start Dates
	DayStart(TempDate) as CalendarDayStart,
	WeekStart(TempDate) as CalendarWeekStart,
	MonthStart(TempDate) as CalendarMonthStart,
	QuarterStart(TempDate) as CalendarQuarterStart,
	YearStart(TempDate) as CalendarYearStart,
	
	// End Dates
	DayEnd(TempDate) as CalendarDayEnd,
	WeekEnd(TempDate) as CalendarWeekEnd,
	MonthEnd(TempDate) as CalendarMonthEnd,
	QuarterEnd(TempDate) as CalendarQuarterEnd,
	YearEnd(TempDate) as CalendarYearEnd,
	
	// Combo Dates
	'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear

	
RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;
	
LET vDateMin = Null();
LET vDateMax = Null();

It is much better and more complete than the previous one I had.

Update: Here is what I am using now. Almost the same, but not quite:

///$tab Calendar
Calendar:
LET vDateMin = Num(MakeDate(2003,1,1));
LET vDateMax = Floor(MonthEnd(Today()));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
  $(vDateMin) + RowNo() - 1 AS DateNumber,
  Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
  
Calendar:
LOAD
	Date(TempDate) AS CalendarDate,

    // Standard Date Objects
	Day(TempDate) AS CalendarDayOfMonth,
	WeekDay(TempDate) AS CalendarDayName,
	Week(TempDate) AS CalendarWeekOfYear,
	Month(TempDate) AS CalendarMonthName,
	'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
	Year(TempDate) AS CalendarYear,
	
	// Calendar Date Names
    WeekName(TempDate) as CalendarWeekNumberAndYear,
    MonthName(TempDate) as CalendarMonthAndYear,
    QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
    
	// Start Dates
	DayStart(TempDate) as CalendarDayStart,
    WeekStart(TempDate) as CalendarWeekStart,
    MonthStart(TempDate) as CalendarMonthStart,
    QuarterStart(TempDate) as CalendarQuarterStart,
    YearStart(TempDate) as CalendarYearStart,
	
	// End Dates
	DayEnd(TempDate) as CalendarDayEnd,
    WeekEnd(TempDate) as CalendarWeekEnd,
    MonthEnd(TempDate) as CalendarMonthEnd,
    QuarterEnd(TempDate) as CalendarQuarterEnd,
    YearEnd(TempDate) as CalendarYearEnd,
    
    // Combo Dates
    'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
    Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
    'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays
	
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());

STORE Calendar INTO C:\ProgramData\QlikTech\Support\QVD\Calendar.qvd;
Advertisements

8 Comments »

  1. […] So here is what I have in the most basic terms: 1. A Calendar (see my previous post) My new and improved Calendar in QlikView 9 […]

    Pingback by In QlikView 9, How to report on what Support Cases were open on any given date using open date and close date? « Rhyous's 127.0.0.1 or ::1 — December 1, 2009 @ 9:33 am | Reply

  2. […] The Calendar table of course has days and months, etc…For more information about my calendar, go here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ […]

    Pingback by Why does the calendar date field and the date field of some other table some times not match up in QlikView? « Rhyous's 127.0.0.1 or ::1 — January 14, 2010 @ 8:56 pm | Reply

  3. Hi,
    Im new to qlikview and i have been looking at your script.
    might be daft but how would i go about using this to create start and end dates for a user to select

    cheers in advance

    Danny Philip

    Comment by Danny — April 7, 2010 @ 4:12 am | Reply

    • Well, that is a feature that doesn’t exactly exist. You have to select the start date, the end date, and all the dates in between. It really isn’t all that hard to select all the dates as you don’t have to select them one at a time.
      1. Add CalendarDate as a Select field.
      2. Select the first date.
      3. Scroll to the second date, press shift and select the second date.

      Also, it is really easy to Create a Select field for Months, Quarters, Years. And selecting any value in a month select field is the same as selecting from the CalendarDate field the first day of the month, the last day of the month, and all the days in between.

      Comment by rhyous — April 8, 2010 @ 6:24 am | Reply

  4. Cheers
    I implemented two calendar controls, and now i can select start and end dates.

    thanks again
    Danny P

    Comment by Danny — April 9, 2010 @ 4:46 am | Reply

  5. Super

    Comment by Mark — September 23, 2010 @ 2:09 am | Reply


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

Blog at WordPress.com.

%d bloggers like this: