dealing with summaries over time

[andres91302]

Hello guys thanks for the help this community is awesome!

I have the meassure the mean of a sample of water microbial upload in the system every 30 minutes, I can then calculate the mean by day, month week ect, but I have not yet found a way to plot the overall mean historically , I wanna be able to plot a timechart with the : accumulated mean up to current date by spans of 1 month, sorry is that is a tongue twister this is whay I am looking for:

if the mean for Jan is 3.5 pp/ml  and the mean of Feb is 3.6 pp/ml I want my chart to display the over all accumulated mean up to Feb which then will include all of the values up to that date and average them ..

index="p_water_s_" OR "test_p_water_OCD"
|search  sample_qc=* date=*

the date field is recorded as an epoch time so I later on give iot structure... thank you guys so much you guys rock

[ITWhisperer]

I am not sure if you want the overall mean across the whole period, which would be plotted as a straight horizontal line with the daily weekly or monthly means you are charting, or an average mean-to-date which would possibly become flatter the more means are taken into consideration, or a rolling average mean which could just consider the last 5 means for example.

For the first case, use eventstats to add the average mean to all the means you are plotting

For the second case use streamstats to add the cumulative average mean to all the means you are plotting

For the third case use streamstats with a window size of 5 for example.

[andres91302]

@ITWhisperer  Thank you Sir would you please tell me if this would be ok For the second case:

index="p_water_s_" OR "test_p_water_OCD"
|search  sample_qc=* date=*
| sort 0 _time
| streamstats mean(sample_q) as mean_to_date 

or should I account for the commulative sums first? thank you so much

[ITWhisperer]

It depends on what you are trying to show, but I suspect you might want to calculate the average mean per reporting cycle, then take a streamstats average of those means. This will give you, for each reporting cycle, the mean for that cycle and the cumulative mean-to-date i.e. two series which you can plot against the same time line. If you do it the other way around, when you do the stats for the average for the reporting cycle, which value are you going to use for the cumulative mean-to-date? If you take the average of the cumulative mean-to-dates you might get a different number to doing it the other way around and my maths isn't strong enough to tell you off the top of my head whether these will be the same or not. If they are different, you would need to decide which of these two you want to show.

[andres91302]

Thank you so much for your quick replay, I want to be able to plot the mean-to-date I want to plot what would be the mean of the water if we use all the meassrements  of the mean daily up until the current date like this:

water_q	mean_by_day	acumm_mean (what I want)
3.6 3.7 3.8	3.7	3.7
3.1 3.2 3.3	3.2	3.45   = mean(3.2 and 3.7)
3.4 3.3 3.9	3.53	3.47 = mean(3.2 and 3.7 and 3.53)
3.6 3.9 3.8	3.76	3.55 = mean(3.2 and 3.7 and 3.53 and 3.76)

[ITWhisperer · Accepted solution]

index="p_water_s_" OR "test_p_water_OCD"
|search  sample_qc=* date=*
| bin span=1d _time
| stats mean(sample_q) as mean_sample_q by _time
| streamstats mean(mean_sample_q) as mean_to_date