OK, so I played around an piping into eval started working. Go figure. Here's my current solution:
search and filter on Category
| stats first(_time) as timestr, values(Y) as y, sum(X) as x by date_year, date_month, date_mday, date_hour, SubCategory
| convert timeformat="%Y-%m-%d %H:00" ctime(timestr) as DateTime
| stats sum(y) as y, sum(x) as x by DateTime, SubCategory
| eval r=x/y
| chart sum(r) by DateTime, SubCategory
The raw data is grouped into 300s clumps comprising several thousand unique events. The first stats command creates the period over which the summaries are built and includes a timestamp which is used by the second stats to build the actual table. Then we eval the average and pipe into chart to produce something that can be put into a graph.
This allows me to keep or drop the SubCategory grouping in the second stats and the chart clauses and calculate the correct average over the Category.
By using stats I've lost the event timestamp so needed to manually re-insert it. I works, but not as nicely as timechart as the X-Axis strings are quite long and a bit ugly when plotting over a week (168 points per series). Changing it to summarise over different time periods would involve changing the 2nd and 3rd lines.
... View more