My new and improved Calendar in QlikView 9

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;

56 Comments

  1. Danny says:

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

    thanks again
    Danny P

    • rhyous says:

      There are actually three separate Calendars you could implement. You have implemented two:
      1. Start Date Caldendar
      2. End Date Calendar

      There is a third that is the interval in-between. For example, what if you want to see how many items there are on a give day that have started but haven't ended? There is a Calendar for that too that I discuss here:
      http://rhyous.com/2009/12/01/in-qlikview-9-how-to-report-on-what-support-cases-were-open-on-any-given-date-using-open-date-and-close-date/

  2. Danny says:

    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

    • rhyous says:

      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.

  3. [...] 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/ [...]

  4. [...] 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/ [...]

  5. [...] 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/ [...]

  6. [...] 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/ [...]

  7. [...] 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/ [...]

  8. [...] 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/ [...]

  9. [...] 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/ [...]

  10. [...] 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/ [...]

  11. [...] 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/ [...]

  12. [...] 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/ [...]

  13. [...] 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/ [...]

  14. [...] 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/ [...]

  15. [...] 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/ [...]

  16. [...] 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/ [...]

  17. [...] 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/ [...]

  18. [...] 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/ [...]

  19. [...] 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/ [...]

  20. [...] 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 [...]

  21. [...] 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 [...]

  22. [...] 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 [...]

  23. [...] 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 [...]

  24. [...] 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 [...]

  25. [...] 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 [...]

  26. [...] 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 [...]

  27. [...] 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 [...]

  28. [...] 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 [...]

  29. [...] 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 [...]

  30. [...] 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 [...]

  31. [...] 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 [...]

  32. [...] 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 [...]

  33. [...] 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 [...]

  34. [...] 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 [...]

  35. [...] 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 [...]

  36. [...] 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 [...]

  37. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  38. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  39. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  40. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  41. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  42. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  43. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  44. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  45. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  46. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  47. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  48. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  49. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  50. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  51. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  52. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

  53. [...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]

Leave a Reply

Powered by sweetCaptcha