Archive

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

New Member

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?

Tags (3)
SplunkTrust

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