4 Replies Latest reply on Jun 2, 2016 1:04 PM by Maximus Decimus Meridius

# Calculating moving average by call center and day of the week (workbook attached)

I have the following data:

What I'm trying to do is calculate a moving average (previous 4 data points) by call center and day of the week. This way I can compare the performance of call centers on a particular day against what I typically see for this call center on that day - essentially removing the weekly trends and getting a fairly flat line that goes up and down when there is a sudden shift in performance.

This is what the graph currently looks like

By subtracting the moving average for call center by weekday I should get a line that's flat and close to zero unless there's a big difference in performance. So for example, if you look at the data, for the Call Center DC on 5/3, I would like to get call counts for the prior 4 Tuesdays for this particular call center and calculate the average. Since the performance varies quite a bit by call center and day of the week this is really the only average that would be useful to compare against.

Any ideas how to achieve this?

• ###### 1. Re: Calculating moving average by call center and day of the week (workbook attached)

Is this what you're trying to do?

• ###### 2. Re: Calculating moving average by call center and day of the week (workbook attached)

I would expect RV to have strong negative numbers for May 30 and 31st as it was 0 on both those days (Mon and Tue) when typically on those days the volume is around 300 (so it should be around -300 ). So I'd expect to see something like this (i.e. the performance being close to the moving average except for those two days)

• ###### 3. Re: Calculating moving average by call center and day of the week (workbook attached)

My bad. I forgot to tell the table calculation to compute across call centers.

• ###### 4. Re: Calculating moving average by call center and day of the week (workbook attached)

Thanks - but not sure I understand. The moving average for a monday May 30th for RV should be about 300. I then want this substracted from each so all lines should hover around 0 except for the days where there's unusual movement. So RV should go down to around -300 on the days it had missing data

EDIT: It also looks like the moving average is by week, vs daily?