7 Replies Latest reply on Jan 31, 2016 9:06 AM by pooja.gandhi

    Phone Call Length Calculation

    Sam Douglas

      I am trying to create a calculated field that displays the monthly grand total of the time that a customer was contacted on the phone for. Customers get multiple phone calls per month.

       

      Each phone call is identified with a phone call ID. The calls start time data is in a field called "Call Start Time" and the end of the call is in a field called "ActualEND (Phone Call)". The below calculation is how I get the Total phone call length for each phone call.

       

                     Call TIme Calculated Field: (DATEDIFF('second',MAX([Call Start Time]),MAX([ActualEnd (PhoneCall)])))/60

       

      I need to create a calculated field that adds each of these phone call lengths together to give the total monthly phone call time per customer. Some customers may have 1 call, while others may have 15 or so.

       

      I can get this calculation by putting the "Call ID" in the Columns shelf and the "Call Time Calculated Field" in the 'marks'. This shows the total length of the each Call ID. Then I "Show Row Grand Totals" to get the total monthly calculation.

       

      I do not need to see each Call ID's call length, I just need to see this "grand total". Is there a way to do this calculation in the background (ie in another calculated field), so that I only see this monthly calculation?

       

      Note: I cannot hide each Call ID because when I change the month (using the quick filter), I have to go back in and re-hide the Call ID's again.

       

      Any help or advice is appreciated! Thanks!

        • 1. Re: Phone Call Length Calculation
          Shawn Wallwork

          Try this:

           

               SUM({ FIXED [Call ID] : (DATEDIFF('second',MAX([Call Start Time]),MAX([ActualEnd (PhoneCall)])))/60 })

           

          Or it might be:

           

               { FIXED [Call ID] :SUM( (DATEDIFF('second',MAX([Call Start Time]),MAX([ActualEnd (PhoneCall)])))/60) }

           

          That should go on a shelf without having [Call ID] in the viz.

           

          --Shawn

          • 2. Re: Phone Call Length Calculation
            Sam Douglas

            Thank you Shawn. I forgot to clarify that I am using a Tableau 8 version and I do not believe that it has the "FIXED" function. Do you know of any other solutions that might work? Thanks!

            • 3. Re: Phone Call Length Calculation
              Shawn Wallwork

              Have you tried:

               

              SUM((DATEDIFF('second', [Call Start Time],[ActualEnd (PhoneCall)]))/60 )

               

              --Shawn

              • 4. Re: Phone Call Length Calculation
                Sam Douglas

                I tried that and it returns the total time of the phone calls since they started (it ignores the filter).

                 

                I can make the first equation work [Call TIme Calculated Field: (DATEDIFF('second',MAX([Call Start Time]),MAX([ActualEnd (PhoneCall)])))/6] if I do a grand total, I just do not need all of the subsets that make up the calculated field. Is there a way to just permanently hide the columns to just show the grand total?

                • 5. Re: Phone Call Length Calculation
                  Shawn Wallwork

                  Yes if you 'select' all the columns you want to hide, and then right-click, you should get an option to 'Hide', and the Grand Totals will remain:

                   

                  Produces:

                   

                  --Shawn

                  1 of 1 people found this helpful
                  • 6. Re: Phone Call Length Calculation
                    Sam Douglas

                    I have tried hiding the columns before, but since each Call ID is unique, when I use the quick filter to go to the next month, the next month's Call ID's populate into the table and then I have to go re-hide them in the columns section. I am trying to find a calculation that will add total customer's Call times that are attached to each customer's Call ID in a  specific month.

                    • 7. Re: Phone Call Length Calculation
                      pooja.gandhi

                      Sam:

                       

                      So if you have data like this

                       

                       

                      Where customer 1 was contacted for 60 mins in December and 10 minutes in January

                      And customer 2 was not contacted in December and was contacted for 65 mins in January

                       

                      Call Time: DATEDIFF('minute', [Start Time], [End Time])

                       

                      You can just group the customers by placing the customer pill on rows and month(start time) on columns. Wouldn't that suffice?

                       

                      So you get the monthly call time per customer without placing the phone call ID in the view? This was just a basic example but if you want to attach your sample data, please feel free to do so.

                       

                      Pooja.