How do I calculate median values for the column for 7 weeks?

[belts]

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!

[DalJeanis]

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")

[vishaltaneja070]

Query looks great. Go Ahead!