12 months rolling

[robmarr789]

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

[sunny_talwar]

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

[robmarr789]

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.

[sunny_talwar]

If you have QV12 or above... try this

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

[robmarr789]

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

[sunny_talwar · Accepted solution]

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)

[robmarr789]

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

[robmarr789]

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

Thanks for your help