topic Re: How to display rolling 12 month on chart using expressions only in QlikView

How to display rolling 12 month on chart using expressions only

vishnumusani — Fri, 03 Feb 2017 05:45:55 GMT

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.

Re: How to display rolling 12 month on chart using expressions only

michelle_le — Fri, 03 Feb 2017 05:58:47 GMT

Hi Vishnu

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

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

Re: How to display rolling 12 month on chart using expressions only

ganeshsvm — Fri, 03 Feb 2017 06:28:12 GMT

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

Re: How to display rolling 12 month on chart using expressions only

vishnumusani — Fri, 03 Feb 2017 06:49:51 GMT

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.

Re: How to display rolling 12 month on chart using expressions only

vinieme12 — Fri, 03 Feb 2017 07:47:39 GMT

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