8 Replies Latest reply on Dec 9, 2014 9:03 PM by Parthasarathi Sengodan

    error reading data?

    Jonathan Schwabish

      Hi,

      I've just started toying with Tableau and I must be doing something fundamentally wrong on my read. My Excel data set has a column for the year, but after I read it in and convert the variable to 'Date' and then view the data, Tableau is converting all of my dates to days in June 1905. Any advice?

       

      Thanks,

      Jon

        • 1. Re: error reading data?
          Andy Cotgreave

          Is the column in Excel formatted as a date, to only show the Year value? (eg Customer Format='yyyy')

           

          It might be that the underlying data is a full date, but you're only seeing the year part of it in Excel. Tableau sees the full version of the data.

           

          If that's not it, can you post an excel file with a couple of columns in it, including the date column that's giving you problems?

          • 2. Re: error reading data?
            Richard Leeke

            Excel (and Tableau) treat dates as a number of days since 30th December 1899 (long story, it was meant to be 1st January 1900, but long ago back in the days of Lotus 1-2-3 someone got it wrong and it stuck).

             

            Anyway, day 2,012 on that counting scheme works out to be 5th July 1905.

             

            So your year column is just being treated as the number of days since the start of the Excel and Tableau view of time.

             

            Easiest thing is just to leave the data type of your [year] field as a number and then create a calculated field [year as date] (say) like this:

             

            DATEADD('year', ([year] - 2000), #January 1, 2000#)

            • 3. Re: error reading data?
              Jonathan Schwabish

              Thanks, Richard. That makes sense, though I couldn't get your formula to work ["All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources."] In addition, I seem to have the same problem even when reading in the data as a text file. I've copied the data below.

               

              Is this an inherent issue in Tableau? Is it the case that whenever a data set comes in with a year variable and the user tries to convert it to a date, Tableau automatically converts it to the Dec 30, 1899 index?

               

              Anyways, any other suggestions are very much appreciated.

               

              Thanks again,

              Jon

               

               

              fsizeyeargrossupgrossdownnet
              119902111
              2199082013
              31990123
              41990101520
              119914145
              2199182013
              31991123
              41991101520
              119926179
              2199282013
              31992123
              41992101520
              1199382013
              2199382013
              31993123
              41993101520
              11994102317
              2199482013
              31994123
              41994101520
              • 4. Re: error reading data?
                Andy Cotgreave

                Can you attach the actual CSV and TWB files you're seeing these problems with? I have never come across this as a problem.

                 

                It might be a problem for support if we can't get much further on this forum!

                • 5. Re: error reading data?
                  Jonathan Schwabish

                  The data .txt file is attached. I haven't been able to load the data so to finish the viz, so I don't have a twb file just yet.

                   

                  Thanks,

                  Jon

                  • 6. Re: error reading data?
                    Andy Cotgreave

                    I'm not sure what's going on on your machine! I just connected to the Text file, converted Year from a Measure to a Dimension and was good to go (attached)

                     

                    I think you might have to email support! Sorry I couldn't help you

                     

                    Andy

                    • 7. Re: error reading data?
                      Richard Leeke

                      I think Jonathan was wanting it to be treated as a date datatype, Andy, rather than just a number.

                       

                      Attached version of Andy's workbook has that calculation I suggested in it. I can't quite think why you would have been getting that error message.

                      • 8. Re: error reading data?
                        Parthasarathi Sengodan

                        hey jonathan,

                         

                        When you import a field from Excel that contains only the year value(YYYY), Tableau recognizes the field as an integer rather than as a date. The date format recognized by Tableau is DD/MM/YYYY.

                         

                        follow the following steps to resolve this problem

                        Select Analysis > Create Calculated Field.

                        • In the Calculated Field dialog box that opens, type a name for the field.
                        • In the Formula box, type the following formula: DATE("01/01/" + STR([Year]))and then click OK.
                        • Use this new calculated field in the view instead of the date field.

                        let me know if this solves your issue:)

                         

                        regards,

                        parthasarathi