9 Replies Latest reply on Dec 24, 2013 12:41 PM by eli.goldner

    converting string to dates

    Antonio Willybiro

      Hi,

       

      i have a dimension with strings like

      2013.01

      2013.02

      ...

      2013.12

       

      i use the following calculated figure to convert those into dates that tableau accepts:

      date("1/"+ mid(str([reporting_period]),6,2) +"/" + left(str([reporting_period]),4))

       

      for some unknown reason when doing that i dont have december anymore. any idea why?

       

      thanks

      Antonio

        • 1. Re: converting string to dates
          Alex Kerin

          Can you share a tbwx where this is happening, even if you just have 10 rows of data in there

          • 2. Re: converting string to dates
            Antonio Willybiro

            attached. on this sample somehow it's october that isnt there anymore. weird.

             

            best

            antonio

            • 3. Re: converting string to dates
              Shawn Wallwork

              Antonio, I see a couple of problems. First your reporting period data has a 2013.1 and 2013.01 that are getting aggregated by your date calc:

               

              Date Problem.png

               

              The other problem is that you're calc makes Tableau think you have 11 days in January instead of 12 months in 2013. (It's that whole US vs. European date problem.) Here's the fix:

               

              1. Change [reporting_period] from a number to a string type (this simplifies the calc).
              2. Put this in your calc: DATE(LEFT([reporting_period],4) + '-' + STR(IF LEN([reporting_period])=7 THEN RIGHT([reporting_period],2) ELSE RIGHT([reporting_period],1) + "0" END))
              3. This Date calc will automatically display US date format. Change this to a custom format of dd/mm/yyyy

               

              Oddly selecting a European date format from the list (01/03/14) doesn't work; it returns 2013/03/01. Not sure why, maybe a bug. But the custom date format work fine:

               

              Date Fix.png

               

              Cheers,

               

              --Shawn

              • 4. Re: Re: converting string to dates
                eli.goldner

                Shawn,

                 

                I have a similar issue, though the solution wouldn't resolve my issue. My dates are stored in a YYYYMM format in a string data type (that's what Tableau calls it, it is a varchar in the source(SQL Server)).

                I tried to user your calc without using the IF statement addition, as mine are always 6 characters long. Can you please look at the calc below?

                 

                DATE(LEFT([billing_period],4)+'-'+RIGHT([billing_period], 2))

                 

                The symbol near this calculated filed shows it as a date type, though when I try to use this dimension all values are NULL.

                 

                Any help would be appreciated.

                 

                Thanks,

                 

                Eli

                • 5. Re: converting string to dates
                  eli.goldner

                  Shawn,

                   

                  I forgot to add a '-01' for the day. Once I added it, it worked fine.

                   

                  Thanks,

                   

                  Eli

                  • 6. Re: converting string to dates
                    Matt Lutton

                    You should be able to convert Strings into dates by simply right clicking on the dimension and choosing "Change Data Type" and then "Date".  This certainly works for the YYYYMM format in a string data type, as my data comes from SQL in this format as well.  Before I start working in Tableau, I set up all my dimensions and measures as discrete/continuous, edit aliases, and ensure all data types are correct.  This helps me reduce the risk of errors and confusion down the road, I think.

                    • 7. Re: converting string to dates
                      eli.goldner

                      Matthew,

                       

                      I wasn’t able to do that… it didn’t recognize YYYYMM for whatever reason… I found the answer to that question, though you may have replied before you saw that I posted a response to myself. Basically, once I added the ‘-01’ for the DD part, the date came across correctly.

                       

                      I appreciate a response nonetheless.. it was quite quick!

                       

                      Thanks,

                       

                      Eli Goldner

                      • 8. Re: converting string to dates
                        Matt Lutton

                        No problem.  If you aren't the database guy who is creating the data sources, I would request that dates be set up properly before they get to Tableau if I were you.  This will make life a lot easier--and some of the most common bugs/errors I've found with Tableau include conversions of fields, particularly when publishing to Server with automated refreshes.  The more things are set up before they get into Tableau, the better, in my opinion.


                        Best of luck!

                        • 9. Re: converting string to dates
                          eli.goldner

                          Actually, I am the database guy so I could have converted it (might still revert to it)… though the data is coming from a rather convoluted system which has counter-intuitive rules for data types in columns…

                           

                          I like your idea…

                           

                          Thanks,

                           

                          Eli Goldner