Master calendar for Qlik Sense

A great feature of Qlik Sense is that your ETL/load script scripts can be moved over from existing QlikView apps with very few modifications. Basically, you need to change the data sources to Sense’s way of doing things* (which I would say is a lot more robust and flexible), then things are likely to work straight off the bat.

An example of this is the creation of a master calendar. The script below is pretty much the same one that is shared in various posts on the Qlik forums, with some minor modifications. The point is that it was taken from a QlikView app, dropped into a Qlik Sense app, and it just works. Very nice.

 

*You can actually put Sense in legacy mode, in which it will accept also QlikView’s syntax for connecting to data sources. I’d say it’s worth the effort to use the new Sense way of connecting though – it’s a one-off investment that makes sense (no pun intended..).

 

Let StartDate = num(date(YearStart(AddMonths(Today(),-48,0)),'YYYYMMDD'));
Let EndDate = num(date(YearEnd(AddMonths(Today(),12,0)),'YYYYMMDD'));
Let CurrentDate=num(date(Today(),'YYYYMMDD'));
Let CurrentYear=Year(Today());
Let LY_YTD=num(AddMonths(Today(),-12,0));
Let DaysToEnd=Monthend(Today())-Today();
Let Timespan=Year(Today())-Year(Date($(StartDate)));

Time:
Load
     *,
     Year(Date) AS [Year],
     Month(Date) AS [Month],
     num(Week(Date),00) as [Week],
     Day(Date) AS [Day],
     WeekDay(Date) AS [WeekDay],
     if(Date<=$(CurrentDate),Date) as Date2,     // Dates until current date
     Year(Date)&num(Month(Date),00) as [Year Month],
     Year(Date) & '-' & num(Month(Date),00) as [YearMonth],
     Dual(WeekYear(Date)&'W'&num(Week(Date),00),WeekYear(Date)&num(Week(Date),00)) as [Year Week2],
     Year(Date)&num(Week(Date),00) as [Year Week],
     Dual(WeekYear(Date)&num(Week(Date),00),WeekYear(Date)&num(Week(Date),00)) as [Year Week3],
     Dual(Year(Date)&'Q'&Num(Ceil(Num(Month(Date))/3)),Year(Date)&Num(Ceil(NUM(Month(Date))/3),00)) as [Year Quarter],
     Dual('Q'&Num(Ceil(Num(Month(Date))/3)),Num(Ceil(NUM(Month(Date))/3),00)) as [Quarter],
     Num(Date) as DateNum,
     Num(Date)-1 as DatePrevNum,
     
    
     1 as _DateCounter,

// Calculation Flags
// All flags below can be used in calculated dimensions and expressions, for example:
// _YTD_TY, used in Expressions Sum([Purchase Qty]*_YTD_TY)

//  YEAR    
     if(InYear (Date, $(CurrentDate), -1),1) as _FULL_LY, // All Dates Last Year
     if(InYear (Date, $(CurrentDate), 0),1) as _FULL_TY, // All Dates This Year
     if(InYearToDate (Date, $(CurrentDate), 0),1) as _YTD_TY,  // All Dates to Date this Year
    // _YTD_TY, used in Expressions Ex. Sum(Sales*_YTD_TY)
   if(InYearToDate (Date, $(CurrentDate), -1),1) as _YTD_LY,  // All Dates to Date Last Year
    // _YTD_LY, used in Expressions Ex. Sum(Sales*_YTD_LY)

//  Quarter
     if(InQuarter (Date, $(CurrentDate), 0),1) as _FULL_TQ_TY, //  All Dates In Current Quarter this Year
     if(InQuarter (Date, $(LY_YTD), 0),1) as _FULL_TQ_LY, //  All Dates In Current Quarter Last Year
     if(InQuarter (Date,$(CurrentDate), -1),1) as _FULL_PRQ, //  All Dates In Previous Quarter
     if(InQuarterToDate (Date, $(CurrentDate), 0),1) as _QTD_TY, //  All Dates In Current Quarter to Date this Year
     if(InQuarterToDate (Date, $(LY_YTD), 0),1) as _QTD_LY, //  All Dates In Current Quarter to Date Last Year
     if(InQuarterToDate (Date,$(CurrentDate), -1),1) as _PR_QTD, //  All Dates In Previous Quarter to Date
    
//  Month    
     If( InMonth (Date, $(CurrentDate),0),1) as _FULL_TM_TY, // All Dates This Month This Year
     If( InMonth (Date, $(CurrentDate),-12),1) as _FULL_TM_LY, // All Dates This Month Last Year
     If( InMonth (Date, $(CurrentDate), -1),1) as _FULL_PRM, // All Dates Previous Month
     If( InMonthToDate (Date, $(CurrentDate),0),1) as _MTD_TY, // All Dates This Month To Date This Year
     If( InMonthToDate (Date, $(CurrentDate),-12),1) as _MTD_LY, // All Dates This Month To Date Last Year
     If( InMonthToDate (Date, $(CurrentDate), -1),1) as _PR_MTD, // All Dates Previous Month To Date
    
//  Week    
     If( InWeek (Date, $(CurrentDate),0),1) as _FULL_TW_TY, // All Dates This Week This Year
     If( InWeek (Date, $(LY_YTD),0),1) as _FULL_TW_LY, // All Dates This Week Last Year
     If( InWeek (Date, $(CurrentDate), -1),1) as _FULL_PRW, // All Dates Previous Week
     If( InWeekToDate (Date, $(CurrentDate),0),1) as _WTD_TY, // All Dates This Week To Date This Year
     If( InWeekToDate (Date, $(CurrentDate), -1),1) as _PR_WTD, // All Dates Previous Week To Date

// Last n days
     If( Num(Date) >= ($(CurrentDate)-7), 1) as _LAST_7D,
     If( Num(Date) >= ($(CurrentDate)-10), 1) as _LAST_10D,
     If( Num(Date) >= ($(CurrentDate)-14), 1) as _LAST_14D
	     

where exists(Date, Date)       // Only create master calendar entries for dates that already exist in the facts tables
;

Load
      Date($(StartDate)+(Iterno()-1),'YYYYMMDD') as Date
      // Date, to be used as key
     
Autogenerate 1
While Date($(StartDate)+(Iterno()-1)) <= Date($(EndDate))
;

 

5 thoughts on “Master calendar for Qlik Sense

  1. Gonza

    In Qlik Sense 2.0, I get this error:

    Se ha producido el siguiente error:
    Unknown statement
    El error se ha producido aquí:
    Autogenerate 1
    While Date(40544+(IterNo()-1)) <= Date(42735,999999988)

    Could you help me?

    Thanks.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *