How to create a Calendar

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

    Michael Solomovich