Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year)

    Hello Friends,


    WTD (Week To Date), MTD(Month To Date), QTD (Quarter To Date) & YTD(Year To Date) are commonly used for any kind of analysis. Below are some expression which you can use while doing the same:

     

    Very important to have the DateKey field in number format by using Floor(Date) AS DateKey. This will gives you numeric equivalent of the respective date. Post that you can derive the required expressions. In this case I have created expressions for WTD, MTD, QTD & YTD for current year as well as previous year. Also included Rolling MTD for 12 months.

     

    WTD

    //Current Year

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(WeekStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}Sales)

    //Previous Year

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(WeekStart(Max(DateKey),-52))) <=$(=AddYears(Max(DateKey),-1))"}>}Sales)

     

    MTD

    //Current Year

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(Max(DateKey)))) <=$(=Num(Max(DateKey)))"}>}Sales)

    //Previous Year

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddYears(Max(DateKey),-1))))<=$(=Num(AddYears(Max(DateKey),-1)))"}>}Sales)

     

    Rolling MTD

    //Current Month

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(Max(DateKey)))) <=$(=Num(Max(DateKey)))"}>}Sales)

    //Current Month - 1

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddMonths(Max(DateKey),-1)))) <=$(=Num(AddMonths(Max(DateKey),-1)))"}>}Sales)

    //Current Month - 2

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddMonths(Max(DateKey),-2)))) <=$(=Num(AddMonths(Max(DateKey),-2)))"}>}Sales)

    .

    .

    .

    //Current Month - 11

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(MonthStart(AddMonths(Max(DateKey),-11)))) <=$(=Num(AddMonths(Max(DateKey),-11)))"}>}Sales)

     

    QTD

    //Current Year

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(QuarterStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}Sales)

    //Previous Year

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(QuarterStart(AddYears(Max(DateKey),-1)))) <=$(=Num(AddYears(Max(DateKey),-1)))"}>}Sales)

     

    YTD

    //Current Year

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(YearStart(Max(DateKey)))) <=$(=Max(DateKey))"}>}Sales)

    //Previous Year

    Sum({<Year=, Quarter=, Month=, Week=, [Order Date]=, DateKey={">=$(=Num(YearStart(AddYears(Max(DateKey),-1)))) <=$(=Num(AddYears(Max(DateKey),-1)))"}>}Sales)

     

    References:

    https://community.qlik.com/docs/DOC-6163

    https://community.qlik.com/thread/215165

     

    I learned all these by referring documents & discussions generated by all Qlik Community members. Thank so much all of you!

     

    Hope this will be of help.

     

    Regards!

    Rahul