topic Rolling Month in QlikView

Rolling Month

Thu, 10 Nov 2016 08:11:25 GMT

Hi Guys,

I have Date field in my dashboard, I need to show Rolling 3 Month and Rolling 6 month data. Could you please help me on this.

Re: Rolling Month

vardhancse — Thu, 10 Nov 2016 16:37:44 GMT

Hi,

Note: The format defined in SET DateFormat and your date field format in Qlikview Table should be same, then only this expressions works.

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -4))<=$(=Date(Max(Date)))’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -5))<=$(=Date(Max(Date)))’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -14))<=$(=Date(Max(Date)))’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Max(Date)-9))<=$(=Date(Max(Date)))’}>} Sales )

Note:

YTD - Year to Date

QTD - Quarter to Date

MTD - Month to Date

WTD - Week to Date

Last YTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -1))<=$(=AddYears(Max(Date), -1))’}>} Sales)

Last QTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -3))’}>} Sales)

Last MTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -1))’}>} Sales)

Last WTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -1))<=$(=Date(Max(Date) -7))’}>} Sales)

Re: Rolling Month

johnw — Thu, 10 Nov 2016 17:12:44 GMT

A possible issue with the above would be IF you wanted to use the Month as a dimension in the chart, and see a rolling 3 months and rolling 6 months for EACH Month. Set analysis isn't sensitive to the dimensions of the chart.

IF that's what you're after, you could use an AsOf table.

AsOfMonth,Month,MonthsBack
Nov 2016,Nov 2016,0
Nov 2016,Oct 2016,1
...
Nov 2016,Jun 2016,5

Dimension = AsOfMonth
3 Month = sum({<MonthsBack={"<3"}>} Something)
6 Month = sum({<MonthsBack={"<6"}>} Something)

And both solutions are assuming you have other calendar fields. In the previous, you have to list them all as shown. In this, you need at least the Month.