Reset cumulative sum per day

[JDriscoll]

Hi,

I have a table with 5 columns:

• One with a timestamp incrementing in 15 minutes per row (time_15m)
• One counting entries (in_count)
• One counting exits (out_count)
• One summing both ins and outs (count_sum)
• One with a cumulative sum of count_sum (cumulative)

time_15m	in_count	out_count	count_sum	cumulative
2022-01-01 00:00:00+00	0	0	0	0
2022-01-01 00:15:00+00	2	0	2	2
2022-01-01 00:30:00+00	0	1	-1	1
2022-01-01 00:45:00+00	3	1	2	3
2022-01-01 01:00:00+00	1	1	0	3
2022-01-01 01:15:00+00	0	2	-2	1

The query for cumulative is as follows:

rangesum( above( sum(count_sum),0,rowno(TOTAL)))

I need cumulative to reset to 0 every time a new day begins, like so:

time_15m	in_count	out_count	count_sum	cumulative
2022-01-01 23:30:00+00	0	0	0	3
2022-01-01 23:45:00+00	0	1	-1	2
2022-01-02 00:00:00+00	0	0	0	0
2022-01-02 00:15:00+00	1	0	1	1
2022-01-02 00:30:00+00	2	1	1	2

The query currently has the cumulative column constantly accumulating regardless of the time_15m value.

How can this query be modified to achieve this?

Thank you.

[JDriscoll]

Hi,

I have a table with 5 columns:

• One with a timestamp incrementing in 15 minutes per row (time_15m)
• One counting entries (in_count)
• One counting exits (out_count)
• One summing both ins and outs (count_sum)
• One with a cumulative sum of count_sum (cumulative)

time_15m	in_count	out_count	count_sum	cumulative
2022-01-01 00:00:00+00	0	0	0	0
2022-01-01 00:15:00+00	2	0	2	2
2022-01-01 00:30:00+00	0	1	-1	1
2022-01-01 00:45:00+00	3	1	2	3
2022-01-01 01:00:00+00	1	1	0	3
2022-01-01 01:15:00+00	0	2	-2	1

The query for cumulative is as follows:

rangesum( above( sum(count_sum),0,rowno(TOTAL)))

I need cumulative to reset to 0 every time a new day begins, like so:

time_15m	in_count	out_count	count_sum	cumulative
2022-01-01 23:30:00+00	0	0	0	3
2022-01-01 23:45:00+00	0	1	-1	2
2022-01-02 00:00:00+00	0	0	0	0
2022-01-02 00:15:00+00	1	0	1	1
2022-01-02 00:30:00+00	2	1	1	2

The query currently has the cumulative column constantly accumulating regardless of the time_15m value.

How can this query be modified to achieve this?

Thank you.