trailing cumulative sum formulaI 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!
AnalyticsData TableScriptingRe: trailing cumulative sum formulaIf 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 formulaGreat! 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 formulaI 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 formulaI 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 formulaJim, 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 formulaIf 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;
