10 Replies Latest reply on Jan 13, 2014 11:28 AM by Derek Arsenault

    Using Time (Hours, Minutes, Seconds) as a Measure

    Nick Zajchowski

      I have a lot of data points that include hours, minutes, and seconds. These are formatted as such in Excel ([h]:mm:ss). When in Tableau, I just want to do easy sums of this data, but, since it is formatted as a date/time, it won't let me move it to the measure column while keeping the time format. Any suggestions?

        • 1. Re: Using Time (Hours, Minutes, Seconds) as a Measure
          Vladislav Grigorov

          Hello Nick,

          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

          • 2. Re: Using Time (Hours, Minutes, Seconds) as a Measure
            Nick Zajchowski

            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?

             

            Nick

            • 3. Re: Using Time (Hours, Minutes, Seconds) as a Measure
              Jonathan Drummey

              Hi,

               

              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))
              ELSE
                  STR(INT((SUM([# of Seconds]) % 3600 ) / 60))
              END

              + ":" +

              //convert seconds back to a string

              IF LEN(STR(INT(SUM([# of Seconds]) % 60))) = 1 THEN
                  "0" + STR(INT(SUM([# of Seconds]) % 60))
              ELSE
                  STR(INT(SUM([# of Seconds]) % 60))
              END

               

              Cheers,

               

              Jonathan

              3 of 3 people found this helpful
              • 4. Re: Using Time (Hours, Minutes, Seconds) as a Measure
                Nick Zajchowski

                Thanks, Jonathan - worked perfectly. I appreciate it.

                 

                Nick

                • 5. Re: Using Time (Hours, Minutes, Seconds) as a Measure
                  William Henzell

                  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?

                  • 6. Re: Using Time (Hours, Minutes, Seconds) as a Measure
                    Dan Cory

                    Another version of the first calculation is:

                    (FLOAT([Time DateTime])+2)*24*60*60

                    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.

                     

                    Dan

                    • 7. Re: Using Time (Hours, Minutes, Seconds) as a Measure
                      William Henzell


                      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.

                      Dashboard.PNG.png

                      • 8. Re: Using Time (Hours, Minutes, Seconds) as a Measure
                        Alex Kerin

                        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.

                        • 9. Re: Using Time (Hours, Minutes, Seconds) as a Measure
                          Derek Arsenault

                          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.

                          • 10. Re: Using Time (Hours, Minutes, Seconds) as a Measure
                            Derek Arsenault

                            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:

                            calc_Seconds

                            RIGHT([THE_hh:mm:ss_FIELD_HERE],2)

                             

                            calc_Minutes

                            MID([THE_hh:mm:ss_FIELD_HERE],FIND([THE_hh:mm:ss_FIELD_HERE],":")+1,2)

                             

                            calc_Hours

                            REPLACE([THE_hh:mm:ss_FIELD_HERE],RIGHT([THE_hh:mm:ss_FIELD_HERE],6),"")

                             

                            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:

                             

                            calc_TOTAL_DURATION

                            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:

                            ROUND(8*60)+(22)+(1/60),1)

                            = 480 + 22 + 0.17

                            = 502.17 minutes

                            2 of 2 people found this helpful