Basically Tableau has built in time intelligence that works in your favor - i.e. it creates a time hierarchy automatically for you for every date/time field in your database. Thus this hierarchy is treated as a dimension, and you cannot perform simple math functions on it, as it cannot be converted to a measure. Actually if you are doing simple math with dates, then you should handle carefully periods having different length - e.g. February has 28 or 29 days, etc. Therefore for date/time fields you have the DATEADD and DATEDIFF functions at disposal. These basically perform addition and subtraction but with dates. Have a lok at these, and if they do not suit your requirements, drop me a line here providing some sample of what you are trying to achieve.
Hope this helps,
Vladi - Thanks for your reply. I was hoping to avoid using a calculation, but understand the reason why Tableau won't transform times to a measure. So, I'm not that well versed in the calculation syntax beyond basic stuff. As I understand it, DATEADD basically allows you to add a fixed value to a part of the date, unlike DATEDIFF which allows you to just take the difference between two time values. I'm struggling to find out how to simply develop a calculation that will sum the times I have in a table. I have a bunch of values, I'll call them "race_times," that are in hh:mm:ss format. Any thoughts on what the syntax would look like on that calculation?
Attached is one way to do this. The DATEPART() function is used in the # of Seconds calc to extract the hours, minutes, and seconds to turn everything into seconds with the following formula:
DATEPART('hour',[Time DateTime]) * 3600 + DATEPART('minute',[Time DateTime])*60 + DATEPART('second',[Time DateTime])
Now there's a measure that can be used. To go from # of Seconds back to HH:MM:SS, here's a hh:mm:ss string calc with the following formula:
STR(INT(SUM( [# of Seconds] )/3600)) + ":" +
// convert minutes back to a string
IF LEN(STR(INT((SUM([# of Seconds]) % 3600 ) / 60))) = 1 THEN
"0" + STR(INT((SUM([# of Seconds]) % 3600 ) / 60))
STR(INT((SUM([# of Seconds]) % 3600 ) / 60))
+ ":" +
//convert seconds back to a string
IF LEN(STR(INT(SUM([# of Seconds]) % 60))) = 1 THEN
"0" + STR(INT(SUM([# of Seconds]) % 60))
STR(INT(SUM([# of Seconds]) % 60))
hhmmss.twbx.zip 27.2 KB
Thanks, Jonathan - worked perfectly. I appreciate it.
Jonathan - do you know of a way to convert the hh:mm:ss text string to a measure so I can total it and sort it as such?
Another version of the first calculation is:
This will be much faster than using DATEPART but will only work for some databases.
The FLOAT converts the Date & Time field to a number, using the rules of the database. The +2 is because times are stored as dates on Excel's zero day, but Jet's zero day is two days later. The *24*60*60 is because you get a result in days and it has to be converted to seconds.
Thanks Dan. To clarify my question - I have 10 columns containing seconds values (eg 900 seconds) that users want displayed in a hh:mm:ss format. This means converting to a string and adding the string column as a row, which removes the ability to sort effectively. In addition to the 10 seconds column we have some additive measures that also need to display on the same row.
What I've done is create a parameter, a sheet for each converted hh:mm:ss column and to the far right, the minute value of the sheet so the user to sort on that column.
I'm hoping there is a better way.
You don't need to use string functions - you can convert the seconds to fractions of a day: [s]/(60*60*24), change this to a datetime, then format the output as hh:mm:ss as a custom format for that calculated field.
It's a good solution but what if you only have a time interval such as 08:22:01 (hh:mm:ss) and no DateTime stamp to reference to like in your example? I need a way to convert the hh:mm:ss format to a calculated FLOAT value (in minutes) such as in this example:
08:22:01 should calculate to 502.02 minutes.
2 of 2 people found this helpful
I think I figured out a way...a little convoluted but it seems to work. hh:mm:ss field needs to be formated as a string for these calcs to work.
Create 3 different calculated values:
Now we can add them all up into an accurate representation of total duration, in this example the number of hours EDIT: minutes rounded to one decimal point:
ROUND((FLOAT([calc_OUTAGE_HOURS])*60) + FLOAT([calc_OUTAGE_MINUTES]) + (FLOAT([calc_OUTAGE_SECONDS])/60),1)
EDIT: So in my example above with a string of 08:22:01 the calculated values would be:
= 480 + 22 + 0.17
= 502.17 minutes