How to find 7days rolling events count

[mnj1809]

Hello,

I want to find the 7 days rolling sum as per the attached sample data. For example in the attached sample data, 7d_rolling_count for 18 Sep should be the sum of previous 7 today_count counts (i.e. from 17 Sep to 11 Sep ) and 7d_rolling_count for 17 Sep should be the sum of previous 7 days today_count (i.e. from 16 Sep to 10 Sep and so on. I am only concerned to calculate the rolling average till first 8 days (i.e till 11 Sep). 

Thanks for your time in advance.

[ITWhisperer]

| makeresults
| eval _raw="max_time_each_day	data_source	today_count	7d_rolling_count
Sep 18,2021 00:00:00 AM EDT	XYZ	54732	458796
Sep 17,2021 18:28:48 PM EDT	XYZ	43688	480685
Sep 16,2021 23:59:57 PM EDT	XYZ	68091	475145
Sep 15,2021 23:59:58 PM EDT	XYZ	126693	404557
Sep 14,2021 23:59:51 PM EDT	XYZ	52283	415302
Sep 13,2021 23:59:57 PM EDT	XYZ	57014	413114
Sep 12,2021 23:59:59 PM EDT	XYZ	56279	406662
Sep 11,2021 23:59:51 PM EDT	XYZ	54748	400261
Sep 10,2021 23:59:49 PM EDT	XYZ	65577	387929
Sep 09,2021 23:59:59 PM EDT	XYZ	62551	424401
Sep 08,2021 23:59:57 PM EDT	XYZ	56105	467392
Sep 07,2021 23:59:51 PM EDT	XYZ	63028	503296
Sep 06,2021 23:59:48 PM EDT	XYZ	54826	547150
Sep 05,2021 23:59:56 PM EDT	XYZ	49827	595720
Sep 04,2021 23:59:54 PM EDT	XYZ	48347	645476
Sep 03,2021 23:59:52 PM EDT	XYZ	53245	690273
Sep 18,2021 00:00:00 AM EDT	PQR	99023	689248
Sep 17,2021 12:42:28 PM EDT	PQR	99096	691634
Sep 16,2021 20:42:59 PM EDT	PQR	98932	694056
Sep 15,2021 20:44:11 PM EDT	PQR	98680	696617
Sep 14,2021 20:42:19 PM EDT	PQR	98397	699328
Sep 13,2021 20:43:22 PM EDT	PQR	98103	702110
Sep 12,2021 20:40:20 PM EDT	PQR	98042	704888
Sep 11,2021 20:38:30 PM EDT	PQR	97998	707618
Sep 10,2021 20:41:00 PM EDT	PQR	101482	706782
Sep 09,2021 20:40:40 PM EDT	PQR	101354	605428
Sep 08,2021 20:41:30 PM EDT	PQR	101241	504187
Sep 07,2021 20:40:34 PM EDT	PQR	101108	403079
Sep 06,2021 20:36:44 PM EDT	PQR	100885	302194
Sep 05,2021 20:39:06 PM EDT	PQR	100820	201374
Sep 04,2021 20:36:35 PM EDT	PQR	100728	100646
Sep 03,2021 20:39:35 PM EDT	PQR	100646	0"
| multikv forceheader=1


| eval _time=strptime(max_time_each_day,"%b %d,%Y")
| sort 0 _time
| streamstats sum(today_count) as rolling_by_data_source time_window=8d by data_source
| eval rolling_by_data_source=rolling_by_data_source-today_count
| table max_time_each_day data_source today_count 7d_rolling_count rolling_by_data_source

[mnj1809]

Hello Dear,

I need the data to be sort by descending order of the max_time_each_day and by data_source. like below:

But the output of your query is as below:

[ITWhisperer · Accepted solution]

OK add the sort

| makeresults
| eval _raw="max_time_each_day	data_source	today_count	7d_rolling_count
Sep 18,2021 00:00:00 AM EDT	XYZ	54732	458796
Sep 17,2021 18:28:48 PM EDT	XYZ	43688	480685
Sep 16,2021 23:59:57 PM EDT	XYZ	68091	475145
Sep 15,2021 23:59:58 PM EDT	XYZ	126693	404557
Sep 14,2021 23:59:51 PM EDT	XYZ	52283	415302
Sep 13,2021 23:59:57 PM EDT	XYZ	57014	413114
Sep 12,2021 23:59:59 PM EDT	XYZ	56279	406662
Sep 11,2021 23:59:51 PM EDT	XYZ	54748	400261
Sep 10,2021 23:59:49 PM EDT	XYZ	65577	387929
Sep 09,2021 23:59:59 PM EDT	XYZ	62551	424401
Sep 08,2021 23:59:57 PM EDT	XYZ	56105	467392
Sep 07,2021 23:59:51 PM EDT	XYZ	63028	503296
Sep 06,2021 23:59:48 PM EDT	XYZ	54826	547150
Sep 05,2021 23:59:56 PM EDT	XYZ	49827	595720
Sep 04,2021 23:59:54 PM EDT	XYZ	48347	645476
Sep 03,2021 23:59:52 PM EDT	XYZ	53245	690273
Sep 18,2021 00:00:00 AM EDT	PQR	99023	689248
Sep 17,2021 12:42:28 PM EDT	PQR	99096	691634
Sep 16,2021 20:42:59 PM EDT	PQR	98932	694056
Sep 15,2021 20:44:11 PM EDT	PQR	98680	696617
Sep 14,2021 20:42:19 PM EDT	PQR	98397	699328
Sep 13,2021 20:43:22 PM EDT	PQR	98103	702110
Sep 12,2021 20:40:20 PM EDT	PQR	98042	704888
Sep 11,2021 20:38:30 PM EDT	PQR	97998	707618
Sep 10,2021 20:41:00 PM EDT	PQR	101482	706782
Sep 09,2021 20:40:40 PM EDT	PQR	101354	605428
Sep 08,2021 20:41:30 PM EDT	PQR	101241	504187
Sep 07,2021 20:40:34 PM EDT	PQR	101108	403079
Sep 06,2021 20:36:44 PM EDT	PQR	100885	302194
Sep 05,2021 20:39:06 PM EDT	PQR	100820	201374
Sep 04,2021 20:36:35 PM EDT	PQR	100728	100646
Sep 03,2021 20:39:35 PM EDT	PQR	100646	0"
| multikv forceheader=1


| eval _time=strptime(max_time_each_day,"%b %d,%Y")
| sort 0 _time
| streamstats sum(today_count) as rolling_by_data_source time_window=8d by data_source
| eval rolling_by_data_source=rolling_by_data_source-today_count
| sort 0 -data_source -_time
| table max_time_each_day data_source today_count 7d_rolling_count rolling_by_data_source