topic Re: 12 months rolling in QlikView

12 months rolling

robmarr789 — Wed, 25 Nov 2020 16:16:04 GMT

Can anyone edit the attached so that the months in the bar chart show rolling 12 month totals. It is currently showing the relative month only. The results also need to adjust when the year is changed.

I don't know where to start.

Thanks

Re: 12 months rolling

sunny_talwar — Thu, 17 May 2018 15:26:33 GMT

You mean to show last 12 months based on selection in Month and Year? or last 12 months when nothing is selected?

Re: 12 months rolling

robmarr789 — Thu, 17 May 2018 16:25:45 GMT

I mean to show the last 12 months based on selection in Year.

Month will always be the dimension selected.

For instance if 2017 is selected January will show sales total feb 16 - jan 2017 and so on for each month in the chart

If 2018 is selected January will show sales Feb 17 - Jan 18 and so on for each month in the chart.

Obviously if the data doesn't go back by up to 12 months then only the available data will be aggregated in the total. This is not a problem as in my final data set at least 12 months data will be available for all of the yearselections I will use.

Re: 12 months rolling

sunny_talwar — Thu, 17 May 2018 16:55:05 GMT

If you have QV12 or above... try this

=Aggr(RangeSum(Above(Sum({<Year, Month, MonthYear, Date>}Sales), 0, 12)), (MonthYear, (NUMERIC)))

Re: 12 months rolling

robmarr789 — Thu, 17 May 2018 16:59:26 GMT

Unfortunately I only have QV 11, will it not work?

Re: 12 months rolling

sunny_talwar — Thu, 17 May 2018 17:01:04 GMT

You will need to make sure that the date is sorted in ascending order in the script and then this will work

=Aggr(RangeSum(Above(Sum({<Year, Month, MonthYear, Date>}Sales), 0, 12)), MonthYear)

Re: 12 months rolling

robmarr789 — Thu, 17 May 2018 17:06:01 GMT

Okay I can manage that. Presumably I need to load a MonthYear column as well?

Re: 12 months rolling

robmarr789 — Thu, 17 May 2018 17:59:56 GMT

I've posted another question about sorting in the script you may like

Thanks for your help