Skip to main content
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Cumulative Rolling Months

Hi All,

I am looking to create a 24 month reporting period where the date behind each month is the sum of the current month and the preceding 11 month. This 24 month period will roll each month, with the last month dropping off.  So for example, month one is October-2018 and is backed by  Nov 2017- October 2018 data, and September 2018 is back by August 2017- October 2018 data and so on so forth.  

I have managed to get the rolling period working for the most recent 12 period. However, I cant get rid of the rest of the axis, and also I would prefer to show it like the second screenshot attachment where I have a current RYTD(first 1-12 months) vs the pervious RYTD(13-24  months).

Attached is a sample of data to show how the file looks that I have used. It's completely fictitious. Where I have order Id it would usually be a  distinct code but I have replaced it with a single letter that is duplicated. 

My data load can be seen in the third screen shot. 

The code I have so far which has generated the first screenshot is as follows:

RangeSum(Above(Aggr(count(OrderNo),RMonthYear),0,12))*AVG({< Day_Ordered = {"$(='>' & Date(AddMonths(Max(Day_Ordered),-12))& '<=' & Date(AddMonths(Max(Day_Ordered),0)))"}>}1)

Thanks for the help, I've been stuck on this for ages. 








10 Replies
Contributor III
Contributor III

@vinieme12  What are the nested functions? I guess if I run the each line individually in the debug script that will work as well?