Frequently asked are the questions about handling Dates - how to create a list of dates, how to create months, or quarters, how to sort month in the right order, etc. I hope the following notes will be helpful.
First step is usually to define Start and End dates of the calendar. I use variable Start, and End, which are defined according to the business rules. For example, hardcoded, from Jan 01, 2000 to Dec 31, 2010:
LET Start = num(makedate(2000,1,1)); LET End = num(makedate(2010,12,1));
Or, start of the year 3 years back to the end of the next year (5 year span): LET Start = floor(YearStart(AddMonths(today(), -12*3))); LET End = floor(YearEnd(AddMonths(today(), 12)));
Or, find max and min dates in one of your data tables, and use them (in this case I usually expand start to the year start and end to the year end): tmp: LOAD min(date) as MinDate, max(date) as MaxDate RESIDENT tablename; LET Start = floor(YearStart(peek('MinDate'))); LET End = floor(YearEnd(peek('MaxDate'))); Drop Table tmp; Or whatever buseness rules there may be...
I usually have a variable number of days for convenience: LET NumOfDays = End - Start + 1;
Next step is to create a list of dates in the numeric form: Date_src: LOAD $(Start) + Rowno() -1 as DateId AUTOGENERATE $(NumOfDays);
Now we can create whatever we want out of this: Calendar: LOAD DateId, // just in case date(DateId) as Date // it will be in format defined in your SET DateFormat=, or in your system format day(DateID) as Day, week(DateID) as Week, month(DateID) as Month, // simple month name; it is dual - numeric and text dual(month(DateID) & '-' & year(DateID), year(DateID) & num(month(DateID), '00')) as MonthYear, // Month-Year format, dual year(DateID) as Year, weekday(DateID) as Weekday, 'Q' & ceil(month(DateID)/3) as Quarter, // in format Q1, Q2, Q3, Q4 dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID), year(DateID) & ceil(month(DateID)/3) as QtrYear // Qn-Year, dual // and whatever else you may want here... RESIDENT Date_src; Drop Table Date_src;
A good habit is to clean up, usually at the end of the whole script:
LET Start = null(); LET End = null(); LET NumOfDays = null();