Set Analysis for Rolling Periods

    Hi All,

     

    Sometimes there may be requirements from users where they want to see the charts by rolling periods based on the selected periods, please check below for various scenarios.

     

    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)

     

    Hope this helps.

     

    Regards,

    Jagan.