6 Replies Latest reply on Aug 11, 2016 8:20 AM by Gurpreet Singh Goraya

    Tableau Calculation with Time

    Gurpreet Singh Goraya

      Hello All,

      I have an excel spreadsheet shows Agent ID, total logged time in HH:MM:SS  with various reason codes. I want to create Tableau worksheet that will show the %age of their total time that each agent utilized in various reason codes. I am having difficulty converting the total logged time into a percentage. The spreadsheet attached herewith.  Please advise. Thanks

       

      GGoraya

        • 1. Re: Tableau Calculation with Time
          Stoyko Kostov

          Hi Gurpreet,

           

          If I understand your question correctly, the following approach may help.

           

          1. Change the data type of Duration to Number (Decimal) (right-click on the field, Change Data Type).

          2. Convert Duration to Measure (right-click on the field, Convert To Measure).

          3, Make sure Default Properties - Aggregation of the field is set to Sum.

          4. Drag Reason Code to Columns, Agent ID to Rows, Duration to Text.

          5. Right-click on Duration in the Marks card, choose Quick Table Calculation, choose Percent of Total.

           

          I'm attaching the workbook I produced. Hope this helps - let me know if you have more questions.

          • 2. Re: Tableau Calculation with Time
            Stoyko Kostov

            Alternatively, you can create a calculated field called DurationInt as DATEDIFF('second', #1900/1/1#, [DurationDT]), and drag this to Sum and use in the Table calculation instead of the converted Duration.

            • 3. Re: Tableau Calculation with Time
              Gurpreet Singh Goraya

              Thank you Stoyko, much appreciated your answer. But I am having difficulty when changing to numbers. The total hours of work do not make any sense to me.Here is the original screenshot

              After converting to numbers, the hours get converted into some decimal numbers and total hour shows as 0.17 instead of 4 hours and 10 minutes.

              Please advise.

              • 4. Re: Tableau Calculation with Time
                Gurpreet Singh Goraya

                I was not able to open worksheet because I have older Tableau 9.1 version. Thanks again

                • 5. Re: Tableau Calculation with Time
                  Stoyko Kostov

                  Yes. The displayed numbers themselves don't make much sense, but the percentages are correct. I assumed this was all you wanted to see.

                   

                  To get the actual totals, you need another calculated field.

                   

                  First, define DurationInt as DATEDIFF('second', #1899/12/30#, [Duration]).

                  Now, define DurationStr as

                  STR(FLOOR(SUM([DurationInt])/3600))+':'+

                  STR(FLOOR(SUM([DurationInt])/60)%60)+':'+

                  STR(FLOOR(SUM([DurationInt])%60))

                  This will display the integer as hh:mm:ss (even if it is more than 1 day - it will show more than 24 hours, respectively).

                   

                  Another subtlety I just noticed is that you had all entries by agent grouped together, and only one Agent ID per group. I don't think Tableau can read that smoothly - it will produce lots of nulls. So, I edited your Excel sheet a bit and added a column "Agent ID by Line", where the Agent ID is repeated for each line/reason code entered - they are not grouped together.

                   

                  Now, drag Reason Code to Columns, Agent ID by Line to Rows, DurationInt to Text and DurationStr to Text. You'll see both total times (hh:mm:ss) and percentages reported.

                   

                  Click on Analytics, drag Grand Total to columns. You'll also see the totals per agent.

                   

                  Attaching a 9.1 workbook - let me know if you have more questions.

                  1 of 1 people found this helpful
                  • 6. Re: Tableau Calculation with Time
                    Gurpreet Singh Goraya

                    Thank you Stoyko. It works accordingly, much appreciated your help.

                     

                    GGoraya