9 Replies Latest reply on Jan 22, 2014 8:49 AM by Joshua Milligan

    Converting to a Date/Time Field

    Mike Lavazza

      I'm working with frequency and severity data and the 'time' field we have stored is the year followed by a decimal representing the month divided by 12.

       

      So they the fields take the form of:

      1986.00000000000

      1986.08333333333

      1986.16666666667

      1986.25000000000

       

      etc.

       

      How would I convert them to date/time field so I can create some xy plots?

       

      The standard coversion lumps everything into 1905.  Do I need to preprocess the field outside of Tableau?

       

      Thanks!

       

      Mike

        • 1. Re: Converting to a Date/Time Field
          Prashant Sharma

          Hi Mike,

          Check these following link. These may help you to understand the concept of date format in Tableau.

          Formatting Numbers and Dates | Tableau Software

          Correcting Date Fields that Tableau Interprets as Strings | Tableau Software

          Supported Date Formats | Tableau Software

          Also, please post your sample workbook so that we can understand your dataset & will suggest you according to that.

           

          Warm Regards,

          Prashant Sharma - India | LinkedIn

          • 2. Re: Converting to a Date/Time Field
            Mike Lavazza

            Here's a sample:

            TrendCoStateYearMonthTimeMaxOfTimeTotTotChgBIBIChgPDPDChgCollCollChgMPMPChgCompCompChgPIPPIPChgWeathWeathChg
            PaidFreqA9198711987.0832014003.2693218720.665862015.915583770.91541926.400463091.338326762.553762131.2341656412.45727910.719229880014.074440.99941831
            PaidFreqA9198721987.1672014003.8755103231.185417186.237432721.054406967.647382221.194817022.509554570.9826892412.2183180.980817550014.302971.01623757
            PaidFreqA9198731987.252014004.8863272921.260821644.960334390.795252576.812857890.890874512.706562691.0785032211.87258470.971703690014.921961.04327648
            PaidFreqA9198741987.3332014002.920328990.597653175.287957491.066048597.401844451.08645222.953121821.0910967810.78591750.908472570013.882220.93032161
            PaidFreqA9198751987.4172014004.8117601891.647677444.690187360.886956336.929746360.936218863.394182321.1493539812.98579351.203958170013.206150.95129996
            PaidFreqA9198761987.52014004.0111174110.833607094.169485230.888980536.22348110.898082093.129085810.921896810.09935670.77772350012.056180.91292168
            PaidFreqA9198771987.5832014002.8955229560.72187445.559514651.333381545.278523250.848162492.668571160.8528277312.83072761.270449990011.709030.97120546
            PaidFreqA9198781987.6672014004.1485452111.432744716.143592961.105059235.24763160.994147672.383570670.8932010913.45274341.048478610010.86080.92755732
            PaidFreqA9198791987.752014004.5658720571.100595954.103357350.66790846.620083771.261537453.10746561.3037018912.7006680.944095020010.474560.96443756
            PaidFreqA91987101987.8332014003.2964982850.721986576.461299521.574637295.627287360.850032653.845384451.2374664610.71686380.84380316009.9345450.94844497
            PaidFreqA91987111987.9172014003.4227617931.038302315.249576850.812464567.790439781.384404123.091417580.803929399.935163350.927058850010.688941.07593664
            PaidFreqA919871219882014002.5319492480.739738674.808394160.915958436.417425170.823756472.509255630.8116844713.11451421.320009930010.007490.93624712
            PaidFreqA9198811988.0832014003.7557046311.48332545.210399381.083604886.124818640.954404373.384055851.348629379.432350440.71922988009.9984870.99910035
            PaidFreqA9198821988.1672014002.5007810240.665862016.727160921.291102746.81444441.112595292.627500550.7764353410.83113311.14829630010.263441.02649911
            PaidFreqA9198831988.252014003.0686897391.227092546.865318331.020537255.472183390.803027083.24277091.234165649.328943140.86130814009.7579660.95075016
            PaidFreqA9198841988.3332014003.7846853161.23332296.284644210.91541927.90792411.445113142.019007940.622618139.296768410.99655108009.4966910.97322445
            PaidFreqA9198851988.4172014004.6883793081.238776524.823704860.767538265.593231330.707294512.387788261.1826542211.91877021.28203368009.3489660.98444464
            PaidFreqA9198861988.52014003.4650173010.739065056.930588731.436777116.097534641.090163142.845041161.1914964210.82210630.9079885009.8396051.05248054
            PaidFreqA9198871988.5832014002.8079511660.810371476.884374760.993331885.489206930.900233822.316610420.814262539.168683990.847218070010.07991.02442135
            PaidFreqA9198881988.6672014004.0749031921.451201594.32106670.627662915.228456960.95249772.038655650.880016619.552887711.041903910010.129671.00493758
            PaidFreqA9198891988.752014004.0113361090.984400354.192817670.970320056.997383861.338326763.46199441.698175179.852268731.031339320010.653391.05170115
            PaidFreqA91988101988.8332014002.8788806320.717686226.872619931.639141146.950657750.993322353.962524791.1445786313.26003071.345886020011.363971.06670054
            PaidFreqA91988111988.9172014004.6999506811.632561855.862784630.8530647.190391841.034490853.23022480.815193599.363005940.706107410011.283250.99289651
            PaidFreqA919881219892014002.8664662070.609892834.370914150.745535515.139065080.71471283.981756571.2326561811.74741541.254662820011.276690.99941831
            • 3. Re: Converting to a Date/Time Field
              Joshua Milligan

              Mike,

               

              Try something like tihs:

               

              DATEADD('month', ROUND(12 * ([Date] - INT([Date]))), DATE("1/1/" + STR(INT([Date]))))

               

              This will start with a date of January 1st for the Year and then add the months based on the decimal places.  I resorted to using ROUND as it seems that anything that wasn't quite the next whole number was being cast as an INT and therefore just truncating instead of rounding.

               

              Regards,

              Joshua

              • 4. Re: Converting to a Date/Time Field
                Mike Lavazza

                Awesome, thanks for the help!

                • 5. Re: Re: Converting to a Date/Time Field
                  Jonathan Drummey

                  This should be more performant than the other options, because it's completely avoiding string conversions:

                   

                  DATEADD('month',([Raw Date]-INT([Raw Date]))*12,DATEADD('year',INT([Raw Date])-1900,#1900-01-01#))

                   

                  It's based on this post Re: Converting Integer to Date by Robin Kennedy, which was presented by Richard Wesley at TCC12.

                  • 6. Re: Re: Converting to a Date/Time Field
                    Joshua Milligan

                    Thanks Jonathan!  That one belongs in the TCL.

                    -Joshua

                    • 7. Re: Re: Converting to a Date/Time Field
                      Joshua Milligan

                      Jonathan,

                       

                      You might take a look at this if you get a chance:

                       

                      There appears to be a slight rounding/truncation issue.  For the date: 1986.08333333333

                       

                      DATEADD('month',([Date]-INT([Date])*12),DATEADD('year',INT([Date])-1900,#1900-01-01#))

                       

                      gives the result 1/1/1966

                       

                      but

                       

                      DATEADD('month',ROUND(([Date]-INT([Date]))*12),DATEADD('year',INT([Date])-1900,#1900-01-01#))

                       

                      gives the result 2/1/1986

                       

                      I suspect the DATEADD function is implicitly casting the argument to an INT causing a truncation issue (.083333333 *12 is .999996 after losing some precision).  Do you know if this is expected behavior?

                       

                      Regards,

                      Joshua

                      • 8. Re: Re: Converting to a Date/Time Field
                        Jonathan Drummey

                        Hi Joshua,

                         

                        I just did a quick test and I agree with your suspicion about DATEADD.

                         

                        Where I'd started is with the sample data Mike provided, and in the first row of that 1987.08333 is month 1, I assumed that should be 1/1/1987 and not 2/1/1987, which is a different result than your calculation. But I messed up with the rounding, thanks for pointing that out. To get back to what I think are Mike's results, I added a -1 offset to the Month DATEADD:

                         

                        DATEADD('month',ROUND(([Time]-INT([Time]))*12)-1,DATEADD('year',INT([Time])-1900,#1900-01-01#))

                         

                        Jonathan

                        • 9. Re: Re: Converting to a Date/Time Field
                          Joshua Milligan

                          Jonathan,

                          Thanks for checking on that!

                          -Joshua