How can I sum data on a rolling 12 months
Answered
Hi
I have a customer who would like a report showing a rolling 12 months consumption but with each month showing the total of the previous 12 months i.e. Oct 18- Sept 19, Nov 18 - Oct 19 etc.
The report for the rolling 12 months has been created (see below) showing the consumption for each month but I am struggling to work out how to sum the previous 12 months on a rolling basis. Please could you advise if this is even possible and if so, how it could be done.
Any help would be appreciated.
Thanks
Wendy
The same question
Hi Simon
I was able to get the report working finally. I tried both of your suggestions but they didn't work for this particular report. The advanced function only gave me a running total which the customer didn't want and if I added a date in to create the Z chart, I ended up with twelve rows per column which didn't work.
In the end I added a sub query for each period using the dynamic date function e.g. (current date - x months) to (current date - x months) for example (current date - 23 months) to (current date - 11 months) and didn't mention specific months in the headers.
I will close this ticket as the the issue is resolved. thanks for your help
Regards
Wendy
Hi Simon
I was able to get the report working finally. I tried both of your suggestions but they didn't work for this particular report. The advanced function only gave me a running total which the customer didn't want and if I added a date in to create the Z chart, I ended up with twelve rows per column which didn't work.
In the end I added a sub query for each period using the dynamic date function e.g. (current date - x months) to (current date - x months) for example (current date - 23 months) to (current date - 11 months) and didn't mention specific months in the headers.
I will close this ticket as the the issue is resolved. thanks for your help
Regards
Wendy
Replies have been locked on this page!