How to display rolling 12 month on chart using expressions only

[vishnumusani]

Hi,

I want to display a rolling 12 month line chart for each month by calculating last 12 months aggregation.E.g: if we are in 2016 Jan then we need aggregate 2015 Feb To 2016 Jan and display it for 2016 Jan like this we need to do for all the current year months.

Attached a sample data

Regards,

MVR.

[michelle_le]

Hi Vishnu

I think the "AsOfMonth" method might help in this scenario.

See link: https://community.qlik.com/docs/DOC-4252#comment-45154

M

[ganeshsvm]

As mentioned by Michelle Le, AsOfMonth is also a good work around for rolling months,

An alternative which I felt easy for me, is to create rolling periods in script and use Set in expression in front end.

In Script, use the following logic in calendar.

Dual(MonthName &' '&Chr(39)& Right(FiscalYear,2),AutoNumber(MonthName &' '&Chr(39)& Right(FiscalYear,2),1)) as RollingPeriod;

and,

Sum({<RollingPeriod={">=$(=max(RollingPeriod)-11)<=$(=Max(RollingPeriod))"}>}SalesValue) in your chart

When you select any Rolling period, it will give you rolling 12 months from the selected period.

Hope this Helps,

-Ganesh

[vishnumusani]

HI Michelle,

Thanks for sharing this information, I have gone thru this earlier as well, but it is not providing me the desired results as shown in attached snapshot in my original post.

Regards,

MVR.

[vinieme12]

create a YearMonth field in your data table to simplify the calculations

example: 201601,201602 and so on

and vStartYearMonth = NUM(year(Today())) & NUM(month(Today()),'00')

and vEndYearMonth = Date(Addmonths( Date($(vStartYearMonth),'YYYYMM'),-11),'YYYYMM')

Just ensure your Month variables are '00'  format meaning Jan is '01' and not '1'

You expression should then be

SUM ( {< YearMonth = {">=$(vStartYearMonth)<$(vEndYearMonth)"} >} Sales)

with YearMonth you will be compare months over different years example 201611 to 201710