3 Replies Latest reply on Aug 7, 2013 12:30 PM by Jim Wahl

    Calculations/Display for multiple measures on a stacked bar

    kellyn.wolff

      Some of the other discussions here have helped me stack multiple measures onto a bar on a single axis.  However, I'm now having trouble creating calculations on this data as well as changing the display.

       

      I'll discuss the calculations first, since that is more dependent on the Measure Values / Measure Names concept.  I have phone time broken down into various categories, where each category is a separate measure (on call, after call, hold time, ring time, available time, etc.)  Each measure is stored as number of seconds.  Using the Measure Values, 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.  I've tried to use table calculations to do this, but I'm not getting the desired result from the various "Compute Using" options.  Is there an easy way to do this?

       

      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)?

       

      I know of solutions to these that I can employ by pre-processing my data in something like SAS, but I'd like to understand if there is a solution in Tableau first.  My end goal is to automate the report, so adding dependencies on other software processes further complicates the overall goal.

        • 1. Re: Calculations/Display for multiple measures on a stacked bar
          Jim Wahl

          Hi Kellyn,

           

          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.

          2013-08-07 16-08-58.png

          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.

          2013-08-07 16-21-42.png

           

          The result looks like this:

          2013-08-07 16-23-43.png

           

          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)

          END

           

          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

          END

           

          For Start Time, I set the Default Table Calc to Advanced > Measure Name and Sorted by MIN(Measure Name Sort Order) ascending:

          2013-08-07 17-22-09.png

           

          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.

           

          2013-08-07 17-41-41.png

           

          Same as the first example above, but with 00:00:00 on the x-axis.

           

          Jim

          • 2. Re: Calculations/Display for multiple measures on a stacked bar
            Shawn Wallwork

            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.

             

            Nice job.

             

            --Shawn

            • 3. Re: Calculations/Display for multiple measures on a stacked bar
              Jim Wahl

              Shawn, Johan,

               

              Thanks for the ataboy. I'll take a look at TabWiki -- looks like a great resource.

               

              Jim