How to obtain a sum of averages

[rrustong]

I have a data set that looks similar to the sample lines below and I'm having a difficult time finding a good way to get averages of some events, and then sum the averages together. I'm hoping that someone can help me work out a search to get the data I need.

Here are a few sample events:

"2015-03-03 22:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=2
"2015-03-03 23:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=3
"2015-03-03 24:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=4
"2015-03-03 22:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=2
"2015-03-03 23:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=2
"2015-03-03 24:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=3
"2015-03-04 22:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=5
"2015-03-04 23:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=4
"2015-03-04 24:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=10
"2015-03-04 22:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=2
"2015-03-04 23:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=4
"2015-03-04 24:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=6

I have worked out a pivot table that gives me the following fields, but the last field does not contain the data that I want (this is what I'm asking for help with):

Time (per day),   locationA,   locationB,   ALL
2015-03-03,       3,           2.33,        2.65
2015-03-04,       6.33,        4,           5.165

The values under "locationA" and "locationB" are an average of the values for that day, which is exactly what I want. However, the values under the "ALL" column are an average of what is under "locationA" and "locationB". What I want in the "ALL" column is a sum of the locationA + locationB values. So the ALL column should contain 5.33 and 10.33 for 2015-03-03 and 2015-03-04 respectively.

This is the command generated by my pivot table:

| pivot myDataModel myDataModelObject avg(VALUE) AS "Average of VALUE" SPLITROW _time AS _time PERIOD day SPLITCOL LOCATION FILTER CLIENT_NAME is ClientA SORT 100 _time ROWSUMMARY 0 COLSUMMARY 1 NUMCOLS 100 SHOWOTHER 0

I do not need to do this within a pivot table, but doing so has gotten me closer than any other search that I've tried to write. I really just need to be able to get the values and will likely need to apply them to a bar graph, but I'm sure I can work out a visualization once I have the data that I'm after.

Just to make sure I'm communicating my requirements clearly, this is what I need to get from the data set:

1. average of the VALUEs for each LOCATION per day
   1. average for locationA on the 3rd,
   2. average for locationA on the 4th,
   3. average for locationB on the 3rd,
   4. average for locationB on the 4th
2. then I need to add all averages for each day to get a total per CLIENT
   1. Sum of "average for locationA on the 3rd" and "average for locationB on the 3rd"
   2. Sum of "average for locationA on the 4th" and "average for locationB on the 4th"

(side note - I can't seem to figure out how to make a nested list...)

[bmacias84]

You could try something like this.

... | eval locationB=if(LOCATION=="locationB",value, isnull())|locationA=if(LOCATION=="locationA",value, isnull()) | bucket _time span =1d | stats avg(locationA)  as locationA, avg(locationB)  as locationB, avg(value) as ALL by _time 

[woodcock · Accepted solution]

Like this:

... | bucket _time span=1d | chart avg(VALUE) over _time BY LOCATION | fieldformat _time=strftime(_time, "%Y/%m/%d") | addtotals fieldname="ALL"

[rrustong]

This appears to do exactly what I need. I figured I would need to use buckets, but couldn't wrap my head around what to do with it after that. Thank you.