Tableau – Using Nested Aggregations to Establish a Weekday/Hour Baseline

Question

Background Information: We have an incident time tracker that tracks how long each user spends with a representative before the issue can be closed. We want to determine the average volume of incidents that are being handled for each hour. To say this in another way: We want to get an hourly baseline for each day of the week that will show us the average total call length within the specific time period. Eg: We want to average the total length of every call on Monday from 9AM-10AM for all the weeks in the database, and the same for other hourly intervals.

The simplest way to think of this is that I want AVG(SUM) for the specific time periods, but Tableau does not allow me to do this.

Tableau Output: This is the desired, target visualization that I am looking for from Tableau.

SQL Query: I have written a SQL query that returns the answer:

We are looking at two columns: start_time (time stamp) and interval_seconds(float)

In the inner query I use the hour_start function which truncates the date/time value to the hour start, so I can group by the hour and day of the week in the outer query.

SQL Results:

Question: Is there a way to solve this problem ENTIRELY in Tableau that would get me the result that I am looking for without having to write any SQL code?

Files Stored on Drive

CSV File: https://drive.google.com/open?id=0B4nMLxIVTDc7NEtqWlpHdVozRXc

Tableau Worksheet: https://drive.google.com/open?id=0B4nMLxIVTDc7M3A4Q0JxbGdlTE0

Answer 1

You can use Level of Detail expressions to compute the SUM(interval_seconds) at the hour level and then use AVG to calculate the number you are looking for.

I created a couple of calculations:

hour which is defined as: DATETRUNC('hour',[start_time])

this should be equivalent to your hour_start(start_time).

and interval_hours which is defined as {FIXED [hour] : SUM([interval_seconds])/3600 }

This calculates the aggregate for each start_time truncated to the hour.

After this, you simply calculate AVG(interval_hours) and use it in your view.

I put a workbook in dropbox: https://www.dropbox.com/s/3hfvz8w529g9f46/Interval%20Time%20Baseline.twbx?dl=0

Although the chart looks similar to yours, the numbers I came up with are somewhat different from the "SQL Results" you show. Was the data you provided slightly different?