If I understood the question right, this is the exact same question as before, except instead of count we're using 'sum'. Note, I renamed the sum(spent) as count to make the naming easier. I literally didn't change anything. This search is superior, I believe, to the other answers, because it only does one search and doesn't involve any math or subsearches...
index="prod" earliest=-7d@d latest=-5s
| timechart sum(spent) as count span=1h
| timewrap d series=short
| addtotals s*
| eval 7dayavg=Total/7.0
| table _time, _span, s0, s1, 7dayavg
| rename s0 as now, s1 as yesterday
Summary: get the data, make the timechart, wrap the data around a day, get the totals for each time period, so we can get the average by dividing by 7. Then clean up the field names.
P.S. If you don't want the average accumulated for the week, just skip the divide by 7 step.
... View more