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

Rolling MTD

//Current Month

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

//Current Month - 1

//Current Month - 2

.

.

.

//Current Month - 11

QTD

//Current Year

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

//Previous Year

YTD

//Current Year

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

//Previous Year

References:

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

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