730773_en-US

liliconcom

Rolling Maximum Sum Over a finite Period

Hi, I have a dataset of about 500,000 rows, each date stamped by the minute. I am trying to find the maximum sum over a 15 minute, 1 hours and 4 hour period i.e.

 

Row 1-15 sum = e.g. 5000 (5000 would be the sum of rows 1-15)

Row 2-16

Rows 3-17 sum = e.g. 8000 (8000 would be the sum of rows 3-17)

.

.

.

 

Rows 10-25 sum = e.g. 7900 (7900 would be the sum of rows 10-25)

 

I would like the tabulation to return 8000. which is the max sum

 

Similar concept with 1hr (rolling sum of 60 rows) and 4hrs (sum of 240 rows)

Basic Data Analysis and ModelingRe: Rolling Maximum Sum Over a finite Period

hi, thanks for the response but I can't seem to get the answer unfortunately. I tried pulling the data into excel to run some tests, but it doesn't give me the same answer especially because its returning some answers with decimals.

Re: Rolling Maximum Sum Over a finite PeriodThe average multiplied by 15 will be the sumRe: Rolling Maximum Sum Over a finite Period

I am looking for a Column Rolling Sum (instead of average). Is that something that can be done?

Re: Rolling Maximum Sum Over a finite Period

Try using Col Moving Average() * 15 to get the moving sum

Col Moving Average( yourcolumn, before=15, after=0) * 15;

Re: Rolling Maximum Sum Over a finite Period

I realized the problem. the before needed to be 14 instead of 15 to include 15 items

Col Moving Average( yourcolumn, before=14, after=0) * 15;

 

Re: Rolling Maximum Sum Over a finite Period

My error......I must have miss typed my response.......I intended to specify 14.