How can I create a Multi-series chart with different sized bins

[stepheneardley]

I've a request from the business to produce a chart with multiple series which usually isn't a problem but in this case they've added some complexity by asking for weekly averages on top of daily totals so I'm trying to work with multiple bins with different sizes.

Series1 - Columns showing the number of daily transactions for the last 30 days
Series2 - A line showing the average daily response time for those daily transactions
Series3 - A weekly average line showing the average response time Mon-Fri

The first 2 are easy but I'm having trouble trying to figure out the last bit. The Statistics tab SHOULD look as follows assuming the 1st and 8th are Mondays;

01/08/2016 - 150 - 2.5 - 2.8
02/08/2016 - 124 - 3.1 - 2.8
03/08/2016 - 167 - 2.1 - 2.8
04/08/2016 - 167 - 4.1 - 2.8
05/08/2016 - 167 - 1.3 - 2.8
06/08/2016 - 167 - 2.1 - 2.8
07/08/2016 - 167 - 4.4 - 2.8
08/08/2016 - 150 - 2.5 - 2.7
09/08/2016 - 124 - 3.1 - 2.7 
10/08/2016 - 167 - 1.9 - 2.7
11/08/2016 - 167 - 3.2 - 2.7
12/08/2016 - 167 - 2.9 - 2.7
13/08/2016 - 167 - 2.1 - 2.7
14/08/2016 - 167 - 3.2 - 2.7

I tried building up the search as follows using appendcols to add the weekly averages data.

index=myIndex sourcetype=sometype source=somesource
| bin _time as TIME span=1d
| convert ctime(TIME)
| stats avg(responseTime) as AvgDailyResponse, count as CallsPerDay by TIME
| appendcols [search index=myIndex sourcetype=sometype source=somesource | bin _time AS TIMEW span=1w | convert ctime(TIMEW) | stats avg(responseTime) as AvgWeeklyResponse]

I'm probably close but just can't finish it off. When I run this search I get a new column called AvgWeeklyResponse that only has a value in the first row rather than weekly averages across the days of each week.

[sundareshr]

Try this

index=myIndex sourcetype=sometype source=somesource
 | timechart span=1d avg(responseTime) as AvgDailyResponse, count as CallsPerDay
 | eval dow=if(strftime(_time, "%a")="Sat" OR strftime(_time, "%a")="Sun", "Weekend", "Weekday")
 | eval weeknum=strftime(_time, "%W")
 | eventstats avg(eval(if(dow="Weekday", AvgDailyResponse, null()))) as WeeklyAverage by weeknum 

[davebrooking]

Stephen

I don't think your appendcols subsearch is matching the requirement you listed, I don't think it handles the Mon-Fri requirement in item 3. You may have intentionally omitted it to illustrate your thinking.

If you do need a Mon-Fri average, you may also need to use something to identify the week number of the events and in association with the associated day number of an event and ignore those events from the average weekly calculation. This answer may help calculate the week number.

You may also need to use streamstats and not appendcols and a subsearch to calculate the weekly average for the the table.

I'm sorry I'm not able to try either of my ideas at the moment, but I thought I'd post an alternative method as food for thought.

Dave

[stepheneardley]

It's helpful to get knocked off a single train of thought sometimes. I'll look into streamstats and check that link regarding calculating the week number. Appreciated Dave.