4 Replies Latest reply on Oct 11, 2019 6:41 AM by Jim Dehner

    Incorrect trasformation of a number into a date

    Noemi Cecchini

      Hi everyone,

      I am almost new on this platform.

      I have two different columns in my excel file: one for the month (written as January, February, ...) and one for the year (2015,...)

       

      When I connect the data-source I am facing the following problems:

      1- Tableau displays "2,015" instead of 2015

      2- when I merge the two columns the year is displayed correctly but the distinction between the different months vanishes and all the months become as if they were January

       

      I tried to change the properties and customise the format number or with some calculation but nothing changes...

       

      Thank you in advance for your help

      Noemi

        • 1. Re: Incorrect trasformation of a number into a date
          Jim Dehner

          good morning

          Tableau does its best guess on the data type as you load the data set

           

          your data has a Month name and a number   not a date

           

          Tableau reads the number as a number and defaults the format to an integer comma at thousands

          just right click the pill and select default format and reset it

           

          the month is read as a string (text)

           

          when you combine the "Month and Years" in a single excel column tableau reads it as a date and cast it as a Date

           

          I would recommend to bring in DATEs or convert them dates in tableau - dates self sequence, sort and allow the use of date functions

          strings and numbers follow different runles

           

          Jim

          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.

          • 2. Re: Incorrect trasformation of a number into a date
            selva kumar

            Hi Noemi,

             

            Your reference..

            Thanks

            selva..

            • 3. Re: Incorrect trasformation of a number into a date
              Noemi Cecchini

              Thank you selva kumar and Jim Dehner for the fast feedback!

              I have solved the problem regarding the format of the number but the other problem still remains open:

               

              as you can see from the picture, once I change the datatype in a date, the result is that it attributes the first day of the month and January as month to all the years.

               

              I tried to solve  this problem using this calculation DATEPARSE('yyyy',STR([year])) but it gives null as result.

               

              I know that I do not have a day to connect to each year (so that it cannot be seen as a real date) but do you maybe know

              - if I could match the month´column with the year so that at least I have the corresponding month

              -or how to transform the number into a date, keeping the year only?

               

              Thank you very much

              Regards

              Noemi

              • 4. Re: Incorrect trasformation of a number into a date
                Jim Dehner

                You have't made the conversion of the string and the number yet

                 

                this will convert the month to a number (once you cast it as a date)

                this will convert the 2 fields and 01 for the first day of the month to a date

                 

                 

                it returns this

                 

                Jim

                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.