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();
[/sourcecode]
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:
[sourcecode language="sql"]
///$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;
[/sourcecode]
Super
Cheers
I implemented two calendar controls, and now i can select start and end dates.
thanks again
Danny P
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/
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
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.
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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/ [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] 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 [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]
[...] UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/ [...]