Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.