trailing cumulative sum formula

[#1]

I have what is likely to be a very easy formula need. I have a temperature dataset and need to calculate a trailing cumulative sum over the past 120 days (known as "degree-heating weeks" since I will convert this sum to weeks by dividing by 7). I know how to get a trailing average, and I know how to get a cumulative sum, but is it possible to get a trailing, 120-day cumulative sum? In this case, when the temperature passes 31.3C, a "score" of 0.25 is given (see "DH-6 hrs" column for formula.). Basically, I want the cumulative sum of these 0.25 values over a 120-day window, but I only know how to partition by date/month/year, etc. (see "#DHWs" script for example), not by an actual window of time. I feel confident that this must be doable!

[#2 · Accepted solution]

If I am interpreting correctly what you want, I believe the below formula will calculate a running 120 trailing cumulative sum

If( Row() == 1,
	holdMatrix = []
);
If( :Name( " Sea Surface Temperature (deg C)" ) > 31.3,
	holdMatrix = holdMatrix || Matrix( 0.25 ),
	holdMatrix = holdMatrix || Matrix( . )
);
If( N Cols( holdMatrix ) == 121,
	holdMatrix[1] = []
);
Sum( holdMatrix ) / 7;

[#3]

Great! That did the trick. I just modified it to reflect the fact that there were eight measuring times per day (i.e., each row is not a unique day, so I multiplied the 121 x 8 = 968 in the evaluation). The data make sense because the value maxes out at the annual max that I had calculated before, before dropping off. Thanks so much. I wish there was a trailing cumulative sum in the formula editor that has similar inputs as the column moving average (which I use ALL the time): weight and N before. Maybe I'll add it to the Wish list. 

[#4]

I have a question that arises from one annoying quirk in the dataset: I need to evaluate the cumulative sum in hours (not weeks, as I mentioned before) over the trailing 120-day period. However, the time intervals are inconsistent (see attached table). In MOST cases, there are 8 measurements/day, but sometimes it is less. Basically, any time the temperature is >31.3, I want to add the time difference to the column. This is easy enough using the "dif" function. However, where I struggle is how to add the "120 days" to the matrix-formula. So in the attached table, I want to do the cumulative sum of the "DHHs" over the trailing 120-day period. Rather than have the number of "rows" in the formula, I need it to track the date instead. Is that possible?

[#5]

I am not exactly sure what divisor you are looking at using, but I used the number of hours over the range of the matrix of data.  Here is the formula I came up with

If( Row() == 1,
	holdMatrix = [];
	dateMatrix = [];
);
If( :Name( " Sea Surface Temperature (deg C)" ) > 31.3,
	holdMatrix = holdMatrix || 0.25;
	dateMatrix = dateMatrix || Informat( :date, "m/d/y" );
);
If( N Cols( dateMatrix ) != 0,
	While( dateMatrix[N Cols( dateMatrix )] > dateMatrix[1] + In Days( 120 ),
		holdMatrix[1] = [];
		dateMatrix[1] = [];
	);
	x = Sum( holdMatrix ) / ((dateMatrix[N Cols( dateMatrix )] - dateMatrix[1]) / In Hours( 1 ));
,
	x = .
);
x;

[#6]

Jim, thanks for looking at my question again, though it seems like the values resulting from your new formula are too low (despite the formula seemingly making sense). But maybe I should have stated my need differently, in which case there may be a simpler (or at least more straightforward solution): I am wanting to know the total number of HOURS in a trailing 120-day period in which the temperature was OVER 31.3C. This usually equates to 3-hr blocks since temperature is only measured every three hours (in most cases), so it's basically a series of sums of 3 (see "DHHs" column in attached table). Therefore, the values could be in the hundreds for the warmest months. So basically, I need a trailing 120-day sum of the DHH column, but it's tricky given the issue of differing numbers of measurements/day!

[#7]

If you just want the sum, then don't divide the sum by anything

If( Row() == 1,
	holdMatrix = [];
	dateMatrix = [];
);
If( :Name( " Sea Surface Temperature (deg C)" ) > 31.3,
	holdMatrix = holdMatrix || 0.25;
	dateMatrix = dateMatrix || Informat( :date, "m/d/y" );
);
If( N Cols( dateMatrix ) != 0,
	While( dateMatrix[N Cols( dateMatrix )] > dateMatrix[1] + In Days( 120 ),
		holdMatrix[1] = [];
		dateMatrix[1] = [];
	);
	x = Sum( holdMatrix ) ;
,
	x = .
);
x;

[#8]

There is something strange going on still. If you look at this data table, you will find your formula (albeit modified slightly) in the "#DHWs (trailing 120 days)" column. There are some stretches of 4-6 months with no temperatures above 31.3C, yet the value never tapers off (such as October 2014-May 2015). If there are no temperatures above 31.3C for 120 days, the "#DHWs" should drop to 0. I feel like your formula SHOULD do this. I wonder if it's an issue arising from traversing from one year to another? Maybe I could jut recode "Day" from 1 to 20,000? 

[#9]

I discovered an issue with the previous formula.  Missing values were causing an issue.  Also, the data needs to be in assending date order.  So please sort the table before applying the formula

If( Row() == 1,
	holdMatrix = [];
	dateMatrix = [];
);
If( Is Missing( :Name( "Date Time (UTC)" ) ) == 0,
	If(
		Is Missing( :Name( " Sea Surface Temperature (deg C)" ) ) == 1, holdMatrix = holdMatrix || 0,
		:Name( " Sea Surface Temperature (deg C)" ) > 31.3, holdMatrix = holdMatrix || :time difference 2,
		:Name( " Sea Surface Temperature (deg C)" ) <= 31.3, holdMatrix = holdMatrix || 0
	);
	dateMatrix = dateMatrix || Informat( :date, "m/d/y" );
	If( N Cols( dateMatrix ) & N Cols( holdMatrix ) != 0,
		While( dateMatrix[N Cols( dateMatrix )] > dateMatrix[1] + In Days( 120 ) & N Cols( holdMatrix ) > 0 & N Cols( dateMatrix ) > 0,
			dateMatrix[1] = [];
			holdMatrix[1] = [];
		);
		If( N Cols( dateMatrix ) & N Cols( holdMatrix ) != 0,
			x = Sum( holdMatrix ),
			x = .
		);
	,
		x = .
	);
,
	x = .
);
(x / 24) / 7;

[#10]

Thanks for your perseverance! That did indeed appear to be an issue, and now the data make more sense. There is still an issue around October 2018 through June 2019: despite the temperature never rising above 31.3C, the value never drops to 0, even by June 2019 (well over 120 days). Can you tell what may be going on there?