5 Replies Latest reply on Sep 8, 2016 11:08 AM by kettan

    Erroneous data for january

    Kumar.0001

      I am making a line graph. I converted a column of strings to the format 01Apr2016 using some if else statements like IF CONTAINS([Pivot field names],"Apr") THEN "01Apr2016" . Then I used DATEPARSE("ddMMMyyyy",[Months]) to convert this new format to date type. Then I used it in my line graph. I am using Fiscal year in my date parse calculated field. The values for all months are correct except January. January's values shows 10 times the real value. Sorry I cannot share the workbook. It would be really helpful if you could guide me towards the possible solutions.

        • 1. Re: Erroneous data for january
          Dmitry Chirkov

          Perhaps, Tableau doesn't know where the month part is?

          Try with 28Apr2016 (i.e. use 28th for the day).

          • 2. Re: Erroneous data for january
            Joe Oppelt

            In spite of your statement that you can't share the workbook, I think you'll have to.

             

            Follow the steps in the video here:

             

            Anonymize your Tableau Package Data for Sharing

             

            I don't need your actual data values (except for the strings you are using to build your dates.)  Just put bogus data in there.

            • 3. Re: Erroneous data for january
              Tristan Hodgkisson

              I'm also experiencing a similar problem.  I have Month dates that are coming from Google Analytics in the format of 'yyyymm'.  I append 01 as the day to the string date so the dates become yyyymm+01  (yyyymmdd).

               

              The concatenation of the strings function correctly as I've tested it by using the string on the canvas.

               

              To convert the string to a date I am using DATE(DATEPARSE("yyyymmdd",[Month Day String])).  This appears to work correctly until you drill down on the date and discover that all the dates are being converted to the 1st of January of the particular year.

               

              The data type on the calculation using the DATEPARSE is Date, I've also tried using the Date Time data type without success.

               

              The problem causes all data to be aggregated against 01-Jan-yyyy in the respective year.

               

              I can't explain why it is functioning this way.  Any assistance would be appreciated.  Version of Tableau is 9300.16.0315.0125.

              • 4. Re: Erroneous data for january
                Tristan Hodgkisson

                I've worked around this issue.  I had to reformat the date into dd-mmm-yyyy so that Tableau could do the conversion just using the Date() function.

                • 5. Re: Erroneous data for january
                  kettan

                  To convert the string to a date I am using DATE(DATEPARSE("yyyymmdd",[Month Day String])).  This appears to work correctly until you drill down on the date and discover that all the dates are being converted to the 1st of January of the particular year.

                  Lowercase (mm) represents minutes.  DATEPARSE needs uppercase for months (MM).

                   

                  CalculationResult
                  DATE(DATEPARSE("yyyymmdd","20160908"))January 8, 2016 (month missing, defaults to January)
                  DATE(DATEPARSE("yyyyMMdd","20160908"))September 8, 2016 (expected result)