I've been able to get these stacked into a single bar so I can see the totals and use Measure Names on the Color shelf to clearly show the types. What I'd like to do is display a label showing each category as a percent of the total.
As you've probably figured out, you can't use measure names / measure values in calculations. Offhand, I don't see an easy way achieve your goal without unpivoting or reshaping the data to make it "tall." If, for example, your current data has seven columns: two dimensions (record, group) and five measures (on call, after call, hold time, ring time, available time), you'd consolidate the measures into two columns and have five times as many rows. It's easier to see than to explain.
You can unpivot the data using either Tableau's Excel plugin or custom SQL. Some databases, including SQL Server, have a built-in unpivot function. If you're using Access or Excel, you can use UNION ALL statements
SELECT [Sheet1$].[Record] AS [Record], [Sheet1$].[Group] AS [Group], "After Call" AS [Measure Name], [Sheet1$].[After Call] AS [Measure Value] FROM [Sheet1$] UNION ALL SELECT [Sheet1$].[Record] AS [Record], [Sheet1$].[Group] AS [Group], "Available Time" AS [Measure Name], [Sheet1$].[Available Time] AS [Measure Value] FROM [Sheet1$] UNION ALL ...
With the unpivoted data, it's easy to drop measure values / names on the cols and colors shelf, as you did before, but now you can crate a new calculated field measure value % =
SUM([Measure Value]) / TOTAL(SUM([Measure Value]))
TOTAL() makes this a table calc and, when entering it, I'd click on the "Default Table Calc" in the upper right and set it to Measure Name.
The result looks like this:
The unpivoting is a bit of a pain, but you could create a separate data connection (or even view in the DB) for just this data.
The other question relates to the formatting of the fields. They are stored as numeric (number of seconds). Is there an easy way to make Tableau recognize and display this as time (HH:MM:SS)?
Tableau doesn't have a time field, but you can convert your seconds Measure Values to datetime using:
DATEADD('second', AVG([Measure Value]), #00:00:00#)
DATEADD adds a date part, in this case seconds, to an existing datetime. Here I'm just using #00:00:00# for the existing date, which results in a date of Dec 30, 1899. You can hide the date part by right-clicking on the measure > default properties > date format >custom "hh:nn:ss".
If you intend to put this time value on the x-axis, then it gets a little complicated, because date values can't be summed. If you use a bar chart, the bars will all overlap (starting at 00:00:00). You could develop a cumulative time using DATEADD('second', RUNNING_SUM(AVG([Measure Value])), #00:00:00#) and the bars, while still overlapping, will look fine until you add labels, which will be centered on the entire bar, not just the visible bar.
One solution is to use a Gantt chart with a start time and duration. I created a few fields to do this
Start Time =
IF MIN([Measure Name]) == "Ring Time" THEN 0
ELSE PREVIOUS_VALUE(0) + LOOKUP(AVG([Measure Value]), -1)
Start Time HHMMSS =
DATEADD('second', [Start Time], #00:00:00#)
Stop Time =
DATEADD('second', [Start Time] + AVG([Measure Value]), #00:00:00#)
Duration Gantt =
[Stop Time HHMMSS] - [Start Time HHMMSS]
Note that these are all table calcs. Start time uses PREVIOUS_VALUE() and LOOKUP(), and the remaining include Start Time and, therefore, when added to the view become table calcs themselves. The times need to be calculated in a particular order. Using a manual sort order may work, but I used a separate calculated field: Measure Name Sort Order =
CASE [Measure Name]
WHEN "Ring Time" THEN 1
WHEN "Hold Time" THEN 2
WHEN "On Call" THEN 3
WHEN "Available Time" THEN 4
WHEN "After Call" THEN 5
For Start Time, I set the Default Table Calc to Advanced > Measure Name and Sorted by MIN(Measure Name Sort Order) ascending:
Now to build the view:
1. Add Group and / or Record to the rows shelf.
2. Add Measure Name (the calculated field, not the internal Measure Names) to color shelf.
3. Add Start Time HHMMSS to columns shelf.
4. Tableau should pick Gantt chart be default (Automatic). If not select Gantt from the marks shelf.
5. Add Duration Gantt to the size shelf.
6. Right-click on the x-axis, click format and select Custom (if you set the custom formatting for this field before, it'll default to this, otherwise enter hh:nn:ss).
7. Add Measure Value % to the label shelf. Click on Label button and change the horizontal alignment to center. You many also need to check and uncheck overlapping marks.
8. Optional: Right-click on the x-axis > Edit Axis > Start at 00:00:00. This is optional, because you may want the x-axis to auto-size if the values will change when you filter or group the data.
Same as the first example above, but with 00:00:00 on the x-axis.
calldata.twbx.zip 25.0 KB
Jim this is so well explained I think it's a great candidate for a TabWiki To make it a little more generic you might consider breaking it in half, 1-Unpivot and 2-Date Manipulations.
Thanks for the ataboy. I'll take a look at TabWiki -- looks like a great resource.