topic How to make a calendar? in QlikView

How to make a calendar?

Sat, 21 Nov 2009 04:15:02 GMT

Yes, I already searched and found info on this:

Wiki
http://community.qlik.com/wikis/qlikview-wiki/how-to-create-a-calendar.aspx

BlogPost:
http://www.quickqlearqool.nl/?p=160&cpage=1#comment-2943

However, they don't work in QlikView 9.

Wiki Failed with this error:

Syntax error, missing/misplaced FROM:
Calendar:
LOAD
 DateId,
 date(DateId) as Date
 day(DateID) as Day,
 week(DateID) as Week,
 month(DateID) as Month,
 dual(month(DateID) & '-' & year(DateID),
 year(DateID) & num(month(DateID), '00')) as MonthYear,
 year(DateID) as Year,
 weekday(DateID) as Weekday,
 'Q' & ceil(month(DateID)/3) as Quarter,
 dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID),
 year(DateID) & ceil(month(DateID)/3) as QtrYear

RESIDENT Date_src
Calendar:
LOAD
 DateId,
 date(DateId) as Date
 day(DateID) as Day,
 week(DateID) as Week,
 month(DateID) as Month,
 dual(month(DateID) & '-' & year(DateID),
 year(DateID) & num(month(DateID), '00')) as MonthYear,
 year(DateID) as Year,
 weekday(DateID) as Weekday,
 'Q' & ceil(month(DateID)/3) as Quarter,
 dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID),
 year(DateID) & ceil(month(DateID)/3) as QtrYear

RESIDENT Date_src

BlogPost one failed with this error:

Script line error:
[TEMP_DATE]:
LOAD
date( date#('$STARTDATE','DD-MM-YYYY')-1 + recno() ,'DD-MM-YYYY') as DATE
AUTOGENERATE (date#('$ENDDATE') - date#('$STARTDATE'))+1

I have really only been using QlikView a short time. I expect shortly, I will understand how to figure these errors out.

Any help would be appreciated.

How to make a calendar?

Bill_Britt — Sat, 21 Nov 2009 04:40:03 GMT

LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));
LET varMaxDate = Num(Peek('OrderDate', -1, 'Orders'));
LET vToday = Num(today());

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************
MasterCalendar:
LOAD TempDate AS OrderDate,
week(TempDate) AS Week,
year(TempDate) AS Year,
month(TempDate) AS Month,
day(TempDate) AS Day,
weekday(TempDate) AS WeekDay,
applymap('Quarters_Map', num(month(TempDate)), null()) AS Quarter,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
week(TempDate)&'-'&Year(TempDate) AS WeekYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag
RESIDENT TempCalendar
ORDER BY TempDate Asc;

How to make a calendar?

Sat, 21 Nov 2009 05:26:06 GMT

Thanks.

That works. The only difference was I got my dates differently and I named my field so they all started with 'Calendar' so all Calendar Fields will be in the same place instead of spread through my table lists.

Also, one should watch the training videos especially this one:

Course - Developer 1 Version 9 e-Learning
- Module 8- Time and dates in QlikView


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

How to make a calendar?

Wed, 19 May 2010 21:56:54 GMT

This is pretty great information...despite my job, I am for the most part a QlikView novice, and this helped solve my central calendar question. Thanks!