Would it be possible to add a packaged workbook?
This makes it easier for us to help you!
Thank you for responding. Tableau workbook attached to my original post.
Luckily for you I cut my teeth on call center analytics within Tableau. One of the things to know is that tableau doesn't know what time is. It only knows date time. That's why you have the seemingly wrong times being from the year 1899. Another thing to know is that when doing date math in tableau 1 is equal to 1 day unless you tell it otherwise. We are not going to tell it otherwise and instead are going to exploit that and do our math in seconds, then convert that to percent of a day and then change our format.
1) The math
SUM((DATEPART('hour',[Active Time])*3600 + DATEPART('minute',[Active Time])*60 + DATEPART('second',[Active Time]) ) )
This looks through each datepart (hours, minute, and second) and multiplies it by the coefficent that would produce the entire amount in seconds. Such that 1:33:10 (1 hour, 33 minutes and 10 seconds) would be 1*3600 + 33*60 + 10. For each service ID we then take that value and sum up all the lines that exist for them in each partition. In the current workbook it is per day. We then divide that sum by 86400 (this is how many seconds are in a day).
You will need to do more if you want to ever show anything that might exceed 24 hours. There are some posts about it but in most call centres this is not a reported metric so you can usually escape the pain.
2) The formatting
Now that that is completed we have a value that probably looks like .1 or .2 or .15 etc if you were to use it in the view. This would mean their active time is 10%, 20% or 15% of an entire day. Now what we want to do is to right click the measure from within the measure pane (you can ctrl+select many of them to do it at one time) then choose default properties -> Number format -> custom and type in hh:mm:ss.
Of course to find the average you can use the above formula which I have named IW. Active Time in a calculated field and say:
[IW. Active Time] / SUM(Answered Calls
Hopefully this helps! These formulas should also work on a call based database instead of a database that has been aggregated to the day and service ID level already if you choose to go that route. It would allow you to return the dailly/service id average call handling, etc.
SampleData-Telephony.twbx 1.2 MB
Howdy Matthias Goossens
Can't speak for Ali's data of course but in the industry this is what happens when someone doesn't log out on their phone. For taking a break you have a set of keys to type, for leaving another set, for a meeting with the boss another one, for the last 2 minutes of the day you have yet another set to type. This allows all of these non working periods to be recorded and it allows the phone system to exclude the analyst from the queue. Of course I don't know this for certain but its my guess. These mistakes are usually corrected by reporting to a manager who is able to override login and loggouts and this is recorded in a historical database and then in further queries only the most recent approval is returned per person, per day.
Thank you so much for this! This worked (sort of)! So I now have the time stamp in the format I want, but sadly I do need to have the time exceed 24 hours. My current view (which groups number of service lines per day - rather than displaying individual service lines per day) is as per below:
In excel, this is simply applying the format: [hh]:mm:ss to the range of cells - but I'm assuming this won't work on Tableau? (it didn't for me, anyway).
I'll have a quick search on the Community now re the format and time exceeding 24 hours - but if you can provide any further assistance, that would be appreciated.
Well that's unfortunate. Jonathan Drummey has already provided a solution here: Formatting Time Durations in Tableau | Drawing with Numbers
It will be the bottom half. He has provided formulas and explanations and deserves the credit for such.
Thanks Carl - I've had a read of the article. So, I have managed to get my data from:
DD/MM/YYYY hh:mm:ss to total number of seconds
by using your formula above (but stripping off the divide by 86400) to give a whole number. I can see this formula works fine for all time durations that are below 24 hours. My problem is now those time duration above 24 hours.
05:34:48 = 20,088 seconds - which is correct and shows up fine on Tableau
25:47:00 = 92,868 seconds but shows on Tableau as 6,420 seconds
Any idea how I can rectify this?
Do not use my formula and instead use the formulas that Jonathan provided
in the later 2/3 of his article.
Thanks Carl - however, I'm still having no luck!!! I've been searching, reading and trying all sorts of random formulas all day!
Sorry to be a pain, but can I be cheeky and ask you to help me with my workbook? Feel free to decline however if this is relatively pain-free for you, then your help would be very much appreciated. What I'm expecting to see is for service ID 497 = 20,088 (I have this through your formula), and service ID 488 should be a total of 92,820 seconds - which should equal 25:47:00
Thank you in anticipation
SampleData-Telephonyv2.twbx 2.4 MB
Can anyone help me?
Can you share a source file? I think your data is stripped off of DATE part while converting from TIME which is not supported by tableau.