How to divide a field by its average?

[brayps]

I have a time chart of count by field

| timechart count by field_name limit=0

I would like to divide each value in the statistics table by the mean of that field. 

Current Output:

Time	A	B
1	1	4
2	2	5
3	3	6

Desired Output:

Time	A	B
1	0.5	0.8
2	1	1
3	1.5	1.2

I can use a `foreach` to perform an operation on every column but I am having trouble configuring a subquery within that to calculate the mean and divide by it.

[johnhuang · Accepted solution]

You can simplify the calculation by using stats. The caveat is that you need to set the span for stats to match the timechart interval, e.g. 1h in the example below:

<base_search>
| bucket _time span=1h
| stats count AS event_ct by _time field_name 
| eventstats avg(event_ct) AS avg_event_ct BY field_name
| eval ratio_of_avg=event_ct/avg_event_ct
| timechart span=1h max(ratio_of_avg) AS ratio_of_avg by field_name

[somesoni2]

Give this a try

| timechart count by field_name limit=0
|  rename * as orig_*
|  eventstats avg(orig_*) as avg_* 
|  foreach orig_* 
    [|  eval "<<FIELD>>"=round('<<FIELD>>'/'avg_<<MATCHSTR>>')] 
|  fields - avg_* 
|  rename orig_* as *

[brayps]

Thank you for your response! I couldn't get this method to work as it was setting the _time field to the default 1970-01-01