3 Replies Latest reply on Jan 18, 2017 4:22 PM by Tom W

    Why does Tableau get confused by the '1111-11-11' Date value...

    Nicholas Drewitt Smith

      Hi,

      I've got a data set which has a number of records that have been assigned dummy date values of '1111-11-11' by the source system. When using the Tableau Date Range slider bar filter on this data set tableau 10 seems to reassign the '1111-11-11' date values to varying obscure historical date values (see example below).

      Date_values.png

       

      Has anyone else encountered this problem before and know why Tableau does this; no record from my underlying data set has the date value of '1394-03-24'?

       

      Im currently working around this problem by excluding these records upon extraction into Tableau; however I'd prefer to not have to do this if can have tableau simply report them as values with a date of '1111-11-11'

        • 1. Re: Why does Tableau get confused by the '1111-11-11' Date value...
          Shawn Wallwork

          Well this is calling on an old and currently tired memory, but yes I have seen and participated in a discussion about historical dates. There is a minimum year that Tableau won't 'go' (recognize) farther back. Maybe it's 1394-3-24 (though that doesn't ring a bell). The discussion was about creating a historical gantt chart starting in BC. Tableau can't do that because of a limit they placed on dates.

           

          You can calculate your way out of this.

           

          If anyone knows where that thread is please post, because we did a lot of testing of where the breakpoint was. Find the breakpoint and assign you 111-11-11 to that date +1, and you won't be filtering these out.

           

          --Shawn

          • 2. Re: Why does Tableau get confused by the '1111-11-11' Date value...
            Jamieson Christian

            Shawn,

             

            On a Windows 7 64-bit system, the minimum possible date value is:

            Note that if the date is being used as a Continuous measure, the minimum is one day later: -547862 (0400-01-02)

             

            That seems to suggest that 1111-11-11 should be recognized by Tableau, so something else must be going on.

             

            Nicholas,

             

            There are two possibilities I can think of.

             

            • If "1111-11-11" is being populated from a string, it's possible Tableau didn't recognize it as a date. (It does look pretty far removed from the usual data values we would be used to seeing.) If you explicitly parse it with DATEPARSE('yyyy-MM-dd'), does the filter then behave as expected?
            • Since you have a range filter, it's quite possible that Tableau capped the total range it would include on the slider, based on what it feels will provide a rational slider experience. (Even with the range it selected, each possible value is going to occupy much less than 1 pixel on the slider, so this user experience is already degraded.)
            • 3. Re: Why does Tableau get confused by the '1111-11-11' Date value...
              Tom W

              1753 often comes up, as that was when Britain switched from Julien to Gregorian calendar. Read more here - tsql - What is the significance of 1/1/1753 in SQL Server? - Stack Overflow

               

              I just loaded a bunch of dates into a table in SQL back to 1000-1-1. You can see they display OK in Tableau below, but when I create a filter slider, it's behaving a bit weird. The textbox at the top won't go prior to 9/14/1752 (you can google this date, there's a bunch of nerdy stuff on why that date is a problem, it's related to the Julien to Gregorian switch). But with that being said, the slider still works OK for me.

               

               

              I can't help but feel that this is a problem with your datasource you are reading from and their support for date datatypes. What type of database is it and what version?

              I think as a workaround, I'd just create a calculated field to change your placeholder date to a more reasonable date which doesn't impact your date.

              I.e. IF [MyDate] = #1111-11-11# then #1900-1-1# else [MyDate] end

               

              Edit; I also think there could be a chance this is some placeholder data as strings being mis-converted. Is the field type coming over as a native date from your DB or are you converting it to string? Can you share some sample records exactly as they are parsed out from your source? Especially if you can isolate a record which has been changed to the 1300's date you're seeing.