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

# Phone Call Length Calculation

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

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

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

Have you tried:

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

--Shawn

• ###### 4. Re: Phone Call Length Calculation

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

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

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

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.