topic Re: trailing cumulative sum formula in Discussions

trailing cumulative sum formula

abmayfield — Sat, 10 Jun 2023 23:23:30 GMT

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!

Re: trailing cumulative sum formula

txnelson — Tue, 08 Dec 2020 17:30:07 GMT

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;

Re: trailing cumulative sum formula

abmayfield — Tue, 08 Dec 2020 17:48:04 GMT

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.

Re: trailing cumulative sum formula

abmayfield — Sun, 13 Dec 2020 19:10:03 GMT

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?

Re: trailing cumulative sum formula

txnelson — Mon, 14 Dec 2020 02:36:46 GMT

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;

Re: trailing cumulative sum formula

abmayfield — Mon, 14 Dec 2020 14:03:03 GMT

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!

Re: trailing cumulative sum formula

txnelson — Mon, 14 Dec 2020 14:35:26 GMT

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;

Re: trailing cumulative sum formula

abmayfield — Mon, 14 Dec 2020 16:42:59 GMT

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?

Re: trailing cumulative sum formula

txnelson — Tue, 15 Dec 2020 14:04:15 GMT

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;

Re: trailing cumulative sum formula

abmayfield — Tue, 15 Dec 2020 22:26:52 GMT

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?

Re: trailing cumulative sum formula

txnelson — Tue, 15 Dec 2020 22:54:14 GMT

If you add the following to the formula

If( Row() == ????, 
     new table("row" || char(row()),new column("Values", set values(holdMatrix)),
     new column("dates", set values(dateMatrix), format("m/d/y")));
);
(x / 24) / 7;

and just replace the ???? with the row number of where you think the data is going funny, you will then see a data table with all of the values that were added into the value, and the dates the values were collected from. That might give you a clue as to what is going on.

Re: trailing cumulative sum formula

txnelson — Tue, 15 Dec 2020 22:53:22 GMT

Below is a new formula that you can use for debugging. It will create the data table for whatever row number you put into the If() statement at the end. If you do not want to create a data table, then just change the row number that you are trying to find, to -1. There will never be a -1 row, so a table will not be created. Anyway, see if you can see what is going on and where an error is occurring.

Make sure the table is sorted correctly and any rows you don't want to be processed are deleted from the table. The script does not honor Excluded rows.

If( Row() == 1,
	holdMatrix = [];
	dateMatrix = [];
	rowMatrix = [];
);
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" );
	rowMatrix = rowMatrix || Row();
	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] = [];
			rowMatrix[1] = [];
		);
		If( N Cols( dateMatrix ) & N Cols( holdMatrix ) != 0,
			x = Sum( holdMatrix ),
			x = .
		);
	,
		x = .
	);
,
	x = .
);
If( Row() == 22159,
	New Table( "row" || Char( Row() ),
		New Column( "RowNum", set values( rowMatrix ) ),
		New Column( "Values", set values( holdMatrix ) ),
		New Column( "dates", set values( dateMatrix ), Format( "m/d/y" ) )
	)
);
(x / 24) / 7;