How can I sum data on a rolling 12 months

EDW Support shared this question 11 months ago
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.

/46ce6452702ae73c56b20a908bf6b4ec

Any help would be appreciated.

Thanks

Wendy

Comments (4)

photo
1

Hi Wendy,


Thank you for your question.


If you wanted to keep the format you have below, you might wish to consider using the Advanced Function - 'Accumulative Total' on the measure that you are interested in from the 'Data' stage of the Report Building process. Note that if you want both the summed total for each month as well as the accumulated total, you will need to drag the field into your report builder two times. You might then wish to use some sort of combination chart to view both measures.


Another solution might be to use a 'Z-Chart' for your desired result. You can find more information about Z Charts here: https://wiki.yellowfinbi.com/display/USER74/Line+Charts#LineCharts-ZChartTutorial. Using a dynamic filter ( -1 year to current ), you could separate each of the values that you've collected in sections, or use the Z Chart to show the overall total.


Let me know how you go with either of these methods.


Regards,

Simon

photo
1

Hi Wendy,


I just wanted to check in and see if you still required further assistance with your query.


Please feel free to respond and I would be happy to provide further assistance. Otherwise, I will go ahead and close this ticket for inactivity, where you will be re-open this question at a later date.


Regards,

Simon

photo
1

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

photo
1

Hi Wendy,


I'm glad that you found a solution!


I'll go ahead and close this question, if you require further help with this in the future, do not hesitate to re-open this ticket by commenting.


Have a wonderful New Year!


Simon