I have data for a call center that shows exactly how long each call sat in a queue before it was answered. I want to graph those answer times, by hour of the day (then I will know they busiest hours and how to staff the call center).
I started by converting the queue answer time to a measure.
but --it still only graphs a count of the answer times. So an answer time of 466 seconds has the same count as an answer time of 28 or 17 seconds, and masks the thing I want to show–the distribution of all the different answer times.
Here is the “count” of the answer times in a bar graph of the data which I do not want:
Here is the set up (aggregated initiation time stamp is just the time stamp arranged so I can choose a time frame (day, week, month) from a dropdown parameter.
This has ramifications for other call center data too (hold times, abandon times, etc.). I’ve never been able to do this in Excel. I was hoping it would be easier in Quicksight. Appreciate any help.
I did! At which point, I have to choose between Sum, Avg, Count or count distinct. I still need the actual measures. (I also have to drill down to a single queue in my parameters (which is fine)).
Here is the chart when I move the answer time to Value and choose AVG:
One option may be to extract the hour from the initiation time stamp using a calculated field formula, something like:
extract(“HH”,{initiation time stamp})
And then use that calculated field on the x-axis of a vertical bar chart, then using Sum of the answertimes field in the Values field well, resulting in something like the following (ignore the different field names and I’m also extracting the Month from the timestamp):
Thanks for looking at this. I can control the initiation time stamp pretty well in Quicksight. I can control the initiation time stamp pretty well in the x axis itself --I can aggregate it from a second up to a year. I also created a drop down parameter that controls whether I want a day, week, month or year.
The crux of my issue is graphing individual values of --say, hold time, answer time, etc. In my mind it makes sense to know the actual numbers, not a total, not an average, to spot outliers. Totals and averages mask the fact that all of a sudden my answer time spiked on just a few calls (i.e., 10 calls took ten minutes each to answer, rather than 100 calls took one minute each to answer–both have the same total, but represent very different situations.
I may be asking too much. With thousands of calls a day the actual numbers of answer time would be thousands of data points to map. That is why people use other metrics like SLA’s and measure what percent of calls were answered within an “acceptable time.”
The closest I have come to see the actual answer times (not total) is to take time of day out of the mix.
Now I can see the actual answer times on the x axis. It just seems odd I cannot flip them over to the Y axis.
I have lowered my expectations on visualizing the actual answer times over Time. But I am still curious if I can get them on the Y axis to see the longest answer times down to the shortest on a graph.
Hello @Coliven, this will likely hit a limitation if you aggregate even by hour depending on the number of calls that are being made, but the most likely way to fix this will be to use your queue time in the value field and then have the queue_name field as your color by. Then, it will sum the queue time but for each distinct queue to make sure there is a bar displayed for each call.
This will likely require you to limit the window of time a user can view the table for but it is the best way to resolve this issue. I will mark this as the solution, but please let me know if you have any remaining questions.
