1 Reply Latest reply on Jun 12, 2014 5:14 PM by Noah Salvaterra

    I have data from a track meet with MM:SS.00 as the numbers.  How can I get tableau to display the intervals not as a clock time?

    john.stjohn

      I have results from a track meet.  The data is a table off kids times in many events

       

      A typical row of excel is

       

      Name  EventName  Age  Lane   Time

       

      the time interval is filled in by the gun clock as

       

      MM:SS.00

       

      where the 00 is hundredths.

       

      how can I format the time function as a dimension or more specifically a measure so that it can be compared between kids for a given event.

       

      It is showing up as strange types of data whether a dimension or a measure.

       

      Thanks!!


        • 1. Re: I have data from a track meet with MM:SS.00 as the numbers.  How can I get tableau to display the intervals not as a clock time?
          Noah Salvaterra

          Interesting issue. I thought this was an easy one, but was mistaken. Usually, my approach to something like this would be to bring these values in as date times and use a datediff from a constant value to peel of the portion of time I was interested in. That works fine for seconds, but hundredths seem to be truncated. That is, hundredths of a second are rounded down. That won't do, since it could easily mean the difference between winning and losing a race!

           

          This issue doesn't seem to involve dimension vs. measure or discrete vs. continuous but the original type of the data. If the date-time is converted to a string inside tableau, the damage has already been done. Actually, this behavior might have to do with the JET driver more than Tableau proper. If, instead, the values are brought in as string type from the start, the minutes and seconds (including fractional parts) can be parsed off using a statement like this:

           

          60*int(left([Str],find([Str],":")-1))

          +float(mid([Str],find([Str],":")+1))

           

          Not letting your data get interpreted as datetime by JET may involve setting up a custom schema.ini file. If you are using a non Jet datasource, you may be able to use additional dateparts in the datediff function inside Tableau, or you could parse this out in a custom sql query.

           

          Let me know if the above solves your problem, if you need help with a schema.ini file or what sort of datasource you are using if it doesn't use the JET driver.

           

          N.