4 Replies Latest reply on Jul 22, 2016 8:03 AM by Joe Oppelt

    How to compare dates?

    Emily Dukas

      I'm trying to filter my data set for things created before a certain date, lets say June 31 2016. I have the Creation Date as a date field and June 31 2016 as a date field as well. However, when I try comparing them in a calculated field "Creation Date <= June 31 2016" it comes back as null, not true or false. I've tried figuring this out many different way and none work, but it seems like an easy filter. Any help would be greatly appreciated thanks so much!

        • 1. Re: How to compare dates?
          ERIC MERRILL

          Where are you applying this filter? The data sheet level?  Its possible you can wrap that with a isnull expression

          • 2. Re: How to compare dates?
            Joe Oppelt

            Check your formatting.

             

            I assume the code you gave in your example above was just pseudo-code, and not the actual code, else that would give you an error.

             

            Question:  Is one field a date-time field and the other just a date field?  If so, a date-time will never equal just a date (at least in my experience.)

             

            You can get around that by wrapping the date-time field in the DATE() function:

             

            IF DATE([date-time field]) <= [date field] ...

             

            It would help if you uploaded a packaged workbook so I can see what you are doing.  Otherwise, all I can do is guess at things.  And that's not fun for me. 

            • 3. Re: How to compare dates?
              Emily Dukas

              They are both date fields, not date time. And I can't upload a workbook due to company privacy policy. I've tried a new approach and now have this as a calculation

               

              DATE([Effective Date]) <= DATE ("2016-06-31")

               

              but it gives me null even though I know there are things with effective dates before June 31

               

              There aren't 31 days in june! problem solved

              • 4. Re: How to compare dates?
                Joe Oppelt

                Emily -- See attached.

                 

                Yes, I noticed the june 31 thing when I started playing with this.

                 

                I created a calc, hard coded to DATE("2016-06-31").  Displayed it, and I kept seeing July 1 instead.  And I thought, maybe it's the format of the date you used inside the quotes.  So I created a second calc using the format I prefer.  Same July 1.  Then I realized that June has only 30 days.  But all the same, Tableau seems to be handling it gracefully.

                 

                I created a calc to use as a filter:  [Order Date] <= [Calculation1]  (and I changed the year to 2012 to accommodate the data used in Superstore.)


                It works perfectly.  Play with it to see.

                 

                I'll say this:  The filter works because it is doing less-than-or-equal.  If you are not grabbing a range but instead a specific date, THEN the June 31 issue could bite you.

                 

                I created this in 8.2 because I don't know what version you are on, but it will upgrade to yours, of course.

                 

                And as for proprietary data, you can still upload sample workbooks to demonstrate what you are encountering, as I did here.  It doesn't have to be your company's data or actual workbook.

                 

                In fact, I made a demo video to show a way to anonymize your stuff if you can only seem to be able to duplicate it on your own stuff.  It is linked here:

                 

                Anonymize your Tableau Package Data for Sharing