Dear all,
There are two columns with data: time
(time scale in steps of 10 minutes) and val
(amount of transactions).
I need to calculate median values (med_val) for the val
column for 7 weeks. The specific example for the point 12.04.2018 15:00:00 med_val = median
( val
by 7 points. 5.04.2018 15:00:00, 29/03/2018 15:00:00, 22/03/2018 15:00:00, 15/03/2018 15:00:00, 8/8/2018 15:00:00, 03/03/2018 15:00:00, 22/02/2018 15:00:00), i.e. so median at 7 same time points on the same days of the week. If there are no data, then we consider that 0 transactions were performed.
The best that I could come up with is:
| timechart span=10m median(val) | timewrap 1w series=exact
Are there any good solutions?
Thanks in advance!
To accomplish this, you need to move each of the data points onto the same _time scale.
Here's one way
index=_audit earliest=-7w@d latest=@d
| bin _time span=10m
| stats count as val by _time
| makecontinuous _time span=10m
| fillnull value=0 val
| rename COMMENT as "The above provides run-anywhere sample data"
| rename COMMENT as "Find the latest time, then recalculate every week to put all records into the same (current) week"
| eventstats max(_time) as maxtime
| eval _time=maxtime - ( (maxtime - _time) % 604800)
| rename COMMENT as "display results. For reference while testing, include number of weeks"
| timechart span=10m median(val) as med_val count as nbr_weeks
| rename COMMENT as "kill prior 6 weeks of now-empty rows"
| where _time > relative_time(now(),"-7d@d")
Query looks great. Go Ahead!