7 Replies Latest reply on Apr 27, 2017 10:25 AM by Christina Reid

    Handling Time Data in Tableau

    Christina Reid

      Hello,

       

      I have phone call data such as waiting, handling, and wrap-up times I am trying to correctly display and perform calculations on in Tableau Desktop 10.1.3.  The data source is an Excel spreadsheet of which these fields are formatted as general.

       

      The field defaults are currently set to text in the Tableau Data pane and the time is displayed as h:mm:ss.  Tableau will not sum these fields nor allow sorting ascending/descending, and all the information I am able to locate suggests the durations have to be converted to seconds and then back to hh:mm:ss which seems counterintuitive.  Is there a more efficient way to obtain the total durations?  What do I need to do differently?

       

      I am familiar with Tableau; however, have not worked with time formatted like this previously.  Any help would be greatly appreciated.  Thank you.

        • 1. Re: Handling Time Data in Tableau

          Hey Christina Reid,

           

          Would you be able to provide us with a draft/example of your data?

          We could play with it and provide you with better support.

          Cheers,

           

          ----------

          Lénaïc RIÉDINGER, Global Community Engineer Tableau

          Tableau Community Forums | Knowledge Base

          If you see a Helpful or Correct response, please mark it thanks to the buttons below the targeted post!

           

          • 2. Re: Handling Time Data in Tableau
            Christina Reid

            Hello Lénaïc Riédinger,

             

            Yes, I have attached an Excel spreadsheet with an example of my data.  The time is Eastern Daylight Time (EDT).  Thank you.

             

             

            Regards,

             

            Christina

            • 3. Re: Handling Time Data in Tableau
              Shawn Wallwork

              Christina,

               

              First let's start with getting your data into Tableau in the correct format:

               

               

              Sometimes Excel seems to format Date fields as Strings. Or at least Tableau interpretes these fields as strings. So first you simply need to click the Abc icon and change it to a Date (or Date & Time) format. You do this when you first connect to the data source (or later you can Edit the data source).

               

              Once Tableau 'realizes' these strings are actual dates, most of your issues should be solved.

               

              But there is still the issue of 'Time Only' fields. When you convert these to Date & Time they'll look like this:

               

               

              For now ignore the date (epoch date). Especially since the 'times' are only going to be used to determine duration. However converting them to Date/Time fields allow you to do the kind of Date math you want to do.

               

              --Shawn

               

              FYI: If you wrap those Time only fields in a FLOAT() function you are on your way to producing a meaningful duration.

              • 4. Re: Handling Time Data in Tableau
                Christina Reid

                Hi Shawn,

                 

                Thank you for your response.  The issue I am having pertains to the 'Time Only' fields which are durations themselves. 

                 

                If I convert the 'Time Only' fields of Waiting Time, Handling Time, and Wrap-Up to Date/Time fields, the actual duration is changed and subsequently incorrect.

                 

                For example, Waiting Time for Contact ID AF29 is 4 seconds denoted by the value 0:00:04 which is in h:mm:ss format.

                 

                I am trying to keep the durations in h:mm:ss format and add them together in Tableau.

                 

                An example of the results I am trying to obtain are for Agent Larry Jones his total 'Handling Time' for phone calls on 4/20/17 equals 0:07:10 (i.e. 7 minutes 10 seconds).  This would be the sum of Larry's 'Handling Time' for each phone call (e.g. 0:01:16 + 0:02:33 + 0:03:21).

                 

                Do you know how to add these durations together in Tableau?

                 

                 

                Regards,

                 

                Christina

                • 5. Re: Handling Time Data in Tableau
                  Christina Reid

                  Hi Shawn,

                   

                  I was able to partially resolve the duration issues in Tableau.

                   

                  First, I had to convert the h:mm:ss fields to seconds and upload this data into Tableau.

                   

                  Tableau then recognizes this field as a number.

                   

                  Next, I created calculated fields for each duration (i.e. Waiting Time, Handling Time, and Wrap-Up) using a formula similar to the one provided for HH:MM:SS at Converting Seconds to HH:MM:SS or DD:HH:MM:SS | Tableau Software.

                   

                  Tableau will now properly display the calculated fields in h:mm:ss format; however, I am still trying to figure out how to total this calculated field when multiple Contact ID's exist.

                   

                   

                  Regards,

                   

                  Christina

                  • 6. Re: Handling Time Data in Tableau
                    Brian Dudley

                    This expression might move you closer. This uses your original data with the durations treated as date times. Not happy with the hard-coded, magical date.

                     

                    MakeTime(0, 0,

                    Sum(

                       DateDiff(

                          'second',

                          date("12/30/1899"),

                          [Handling Time (Total)])

                       )

                    )

                     

                    I don't think this will help if the total is greater than a day.

                     

                    It does produce total results:

                     

                     

                    Update: I forgot to add that the output needs to be formatted to only show the time in 24 hour format.

                    • 7. Re: Handling Time Data in Tableau
                      Christina Reid

                      Brian,

                       

                      Thank you.  This is another way of displaying the durations properly.  If I want to then add the durations together in your print screen to get a total is where I am having difficulty.

                       

                       

                      Best Regards,

                       

                      Christina