Te easiest way to do this is with formatting, as you suggested.
What you need to do is turn your time into a fraction of a day, and then format it as hours (if you need that much), minutes and seconds.
To turn it into a fraction of a day, just divide your time in seconds by 86400 (the number of seconds in 24 hours). Then just format it with the string "hh:mm:ss" or just "mm:ss". Like this:
Just want to say thank's to Richard! :-)
Simplified steps for those like me who need a little more help:
- Create the following calculated field calculation: (sum([Visit Duration])/sum([Visits]))/86400
- Ensure the measure you created is continuous or the color green. If it's blue or discrete right click on the calculated field measure you created and convert it to continuous.
- Right click on the calculated field measure you created and click: Default Properties > Number Format...
- Change the format to: Custom: hh:mm:ss
And you have the magic!
Hi Richard --
I followed these steps to get the time as a fraction of a day (sum([seconds]) / sum([events])) / 86400)
but when I try to display as m:ss (or even mm:ss), it decides that "m" is month instead of minute. It works fine when I use hh:mm:ss, but not when I leave off the "hh:" I really need the clean look without the hh. Can you help, please? TIA!
This won't work:
But this will:
1 of 1 people found this helpful
Sorry, I led you astray with that original posting. You need to use "nn:ss" instead of "mm:ss".
I think Tableau (and perhaps Excel) infers minutes when surrounded by hours and seconds, but not when just followed by seconds.
Richard, you just made my day. Been trying to figure this one out and you just saved me a lot of time.
Don't forget to cast your number of seconds to a Float (ex: with the FLOAT function) before dividing by 86400, otherwise your time will be rounded.
This works but Tableau resets clock if number seconds is greater than +80000. How can I avoid that?