Hi,
I'm trying to replicate the Control chart we do in excel but have researched and cannot find a solution.
Background - call centre work and want to do a control chart on AHT (Average Handling Time). Each month we plot on a control chart the agents (people taking calls) AHT for the month along with the mean and some defined upper and lower control limits.
The data source is at a granular level. Each row of data represents 1 agent on a single day and shows total handling time and calls answered. So to get AHT I need a measure which is the sum of the handling time divided by the calls answered. It has to be a meassure so that the AHT for the month is correct. I cannot to a calculalted column because all that does is give me the correct AHT by agent by day but to get the monthly AHT you cannot to an average of the daily AHTs cause thats incorrect (not weighted AHT)
eg. day 1 - Calls 2 handling time 500sec - AHT equals 250sec and day 2 Calls 5 handling time 1000sec AHT 200. You cannot just average the daily 250sec 200sec AHT (which gives you 225sec). the correct AHT is the (500 + 1000) / (2 + 5) = 214sec
So this is why AHT is a measure
Here is the chart I need. For September I need
-> All the Agents AHT plotted which is simple
-> the mean AHT for the entire popluation for the entire month. This needs to be a static line.
-> Upper and lower control lines (again static)
Problems
-> cannot get a statis mean line
-> cannot get a static standard deviation measure to apply to the static mean line
In excel this is simple because we use the granular data to create calculated data table with what we need and simply chart the calculated data table.
I understand there may not be a solution to this without extremaly complex R scripting which I have no idea how to do so I'm hoping this may spark developers to assist in the future
