topic Re: Rolling Maximum Sum Over a finite Period in Discussions

Rolling Maximum Sum Over a finite Period

nv11 — Thu, 07 Mar 2024 23:04:21 GMT

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)

Re: Rolling Maximum Sum Over a finite Period

txnelson — Thu, 07 Mar 2024 23:23:12 GMT

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

nv11 — Thu, 07 Mar 2024 23:30:57 GMT

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

txnelson — Fri, 08 Mar 2024 00:54:53 GMT

The average multiplied by 15 will be the sum

Re: Rolling Maximum Sum Over a finite Period

nv11 — Fri, 08 Mar 2024 04:40:27 GMT

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 Period

nv11 — Fri, 08 Mar 2024 05:24:27 GMT

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

txnelson — Fri, 08 Mar 2024 05:26:57 GMT

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