3 Replies Latest reply on Jan 17, 2019 7:51 AM by Jim Dehner

    Query re time analysis - version

    James Brewerton

      Please see the attached packaged workbook which is some analysis of triathlon data.


      I followed the tips at this link (https://kb.tableau.com/articles/issue/extract-time-from-date-and-time-field ) to split out a date and time field into just time.


      However, as you can see in the packaged workbook when I try and show the times for each section the results appear incorrect.


      As an example, see the 'Swim master' number shown in the analysis tab is '23:20:18'. However, when I 'View data' the Swim Master cell for 'James Brewerton' is clearly '00:39:42'. It is the same problem with the T1 data too. See image below.


      Tableau query.JPG


      Could someone take a look and let me know where this is going wrong?


      Thanks in advance



        • 1. Re: Query re time analysis - version
          Jim Dehner

          Good morning


          As a fellow Triathlete I feel the frustration in your post - see below -

          this is a portion of the data table - note the dates - they are are all based on 12/30/1899 - that is throwing off all the calcualtions


          This data has been converted from its original format to a date time format and the conversion was not correct


          What and can you post a small sample of the data




          • 2. Re: Query re time analysis - version
            James Brewerton

            Thanks Jim.


            Here is the dataset. It is publicly available so I am happy to share on here.

            • 3. Re: Query re time analysis - version
              Jim Dehner

              Hi this is the best I am able to do - it is brute force but will return this


              here is the problem with the excel data - the minutes and seconds have been converted to date and time starting at 12:00 AM

              I converted them to minutes a decimal parts using this

              there may have been and easier way but I am not certain

              I brought the data into Tableau and pivoted around the identification and demo data

              then converted that back to minutes and seconds



              then concatenated them into a string



              I played with using datediff on the original date/time values but it kept returning to the clock time values starting at midnight


              Remember to have fun on race day - makes all the training worthwhile


              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.