CODEWITHSUNDEEP
timenullformattableau-apicalculated-field
Phil
Phil

Reputation: 73

How can you add FLOAT measures in Tableau formatted as a time stamp (hh:mm:ss)?

The fields look as described above. They are time fields from SQL imported as a varchar. I had to format as date in tableau. There can be NULL values, so I am having a tough time getting over that. Tableau statement I have is only ([time spent])+([time waited])+([time solved)].

Thank you!

Upvotes: 0

Views: 3640

Answers (2)

Alex Blakemore
Alex Blakemore

Reputation: 11921

The best approach is usually to store dates in the database in a date field instead of in a string. That might mean a data prep/cleanup step before you get to Tableau, but it will help with efficiency, simplicity and robustness ever after.

You can present dates in many formats, including hh:mm, when the underlying representation is a date datatype. See the custom date options on the format pane in Tableau for example. But storing dates as formatted strings and converting them to something else for calculations is really doing things the hard way.

If you have no choice but to read in strings and convert them to dates, then you should look at the DateParse function.

Either way, decide what a null date means and make sure your calculations behave well in that case -- unless you can enforce that the date field not contain nulls in the database.

One example would be a field called Completed_Date in a table of Work_Orders. You could determine that a null Completed_Date meant the work order had not been fulfilled yet, and thus allow nulls for that field. But you could also have the database enforce that another field, say Submitted_Date, could never be null.

Upvotes: 0

bitlamas
bitlamas

Reputation: 742

If you only want to use the result for a graphical visualization of what took the longest, you can split and add all the values into seconds and using it into your view. E.g.

Sum of HH:MM:SS

In this case the HH:MM:SS fields are Strings for Tableau.

The formula used to sum the three fields is:

//transforms everything into seconds for each variable
zn((INT(SPLIT([Time Spent],':',1))*3600))
+
zn((INT(SPLIT([Time Spent],":",2))*60))
+
zn((INT(SPLIT([Time Spent],":",3))))
+
zn((INT(SPLIT([Time Waited],':',1))*3600))
+
zn((INT(SPLIT([Time Waited],":",2))*60))
+
zn((INT(SPLIT([Time Waited],":",3))))
+
zn((INT(SPLIT([Time Solved],':',1))*3600))
+
zn((INT(SPLIT([Time Solved],":",2))*60))
+
zn((INT(SPLIT([Time Solved],":",3))))

Quick explanation of the formula:

  1. I SPLIT every field three times, one for the hours, minutes and seconds, adding all the values.
  2. There is an INT formula that will convert the strings into integers.
  3. There is also a ZN for every field - this will make Null fields become Zeros.

You can also use the value as integer if you want, e.g. the Case A has a Total Time of 5310 seconds.

Upvotes: 1

Related Questions