Calendar with period flags

    The master calendar table is probably one of the most common examples and things all QlikView developers do in their project. With this example I want to give an example of how to compose a calendar by using pre-ceeding loads, format functions and flags.

     

    First I want to introduce a format variable that is not part of the default format variables. The LongMonthNames variable contains the month names represented by the MMMM format string.

     

    SET LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';
    
    

     

    To generate a calendar we need to define the start date and the end date, so that we then can decide the length of the calendar.

     

    LET vCalendarStart = Date#('2015-01-01');
    LET vCalendarEnd = YearEnd(today());
    LET vCalendarLength = $(#vCalendarEnd) - $(#vCalendarStart) + 1;
    
    

     

    A pre-ceeding load is composed by multiple LOAD statement that are piled on top of each other. In this construction the load logically starts from the lowest LOAD statement. In this case the AutoGenerate based LOAD. This generates the number of records required to cover the calendar length, and defines a date on each row base don the calendar start date and the current record number.

     

    LOAD
      Date($(#vCalendarStart) + RecNo()-1) AS Date 
    AutoGenerate $(#vCalendarLength);
    
    

     

    The initial LOAD now acts as the input or source fro the next LOAD statement. In this LOAD some basic characteristics as defines fro each date, like which year and month they belong to. I often prefer to have week number as a two digit value, so to accomplish this I use the Num() format function and specify a two digit format.

     

    LOAD
      Date,
      Year(Date) AS Year,
      Num(Month(Date), '00') AS Month,
      Num(Day(Date), '00') AS Day,
      Num(Week(Date), '00') AS Week
      ;
    
    

     

    Consequently, the output of the second LOAD becomes the input for the next LOAD. Here the MMMM is used to create a long month name.

     

    LOAD
      *,
      Num(Ceil(Month/3), 'Q0') AS Quarter,
      WeekDay(Date) AS WeekDay,
      Date(MonthStart(Date), 'YYYY MMM') AS YearMonth,
      WeekName(Date) AS YearWeek,
      Date#(Date(Date, 'MMMM'), 'MMMM') AS MonthName
    ;
    
    

     

    Now the actual calendar is generated. On top of the calendar I often add flags to allow for easier analysis that is time line based. In this example I added flags for the latest 30 day period, the current week and the remaining days of the year. The flag always consists of 1 if the flag is true, and 0 if the flag is false.

     

    LOAD
      *,
      If(Date>=(Today()-30) AND Date<Today(), 1, 0) AS Is30DayPeriod,
      If(Week=Week(Today()), 1, 0) AS IsCurrentWeek,
      If(Date<=YearEnd(today()) AND Date>Today(), 1, 0) AS IsLeftCurrentYear
    ;
    
    

     

    The flags enable simple set expressions, like counting the number of Mondays with the last 30 day period. The expression is easy to implement, and very easy to interpret.

     

    count({<Is30DayPeriod={1}, WeekDay={"Mon"}>} Date)
    
    

     

    The flag being 1 or 0, also allows for a aggregation to see how many occurrences there are. Without any complicated expression we can see the remaining number of days of the current.

     

    sum(IsLeftCurrentYear)
    
    

     

    See Customized flags for more guidance on making smarter flags