6 Replies Latest reply on Aug 10, 2016 9:05 PM by David Li

    Unexpected MONTH(Date) Filtering

    David Li

      Hi, everyone! I ran into an issue with date filtering today, and I can't figure out what could be causing it (unless it's a bug in Tableau).

       

      Basically, I have a database with monthly data. The dates are always set to the last day of the month. I want to allow users to select the range of dates displayed by using a two-sided slider, so I used month (as a date value, not part) as the filter.

       

      However, the filter doesn't work correctly. If the lower bound shows "February 2015", it actually filters out the 2/28/15 value. Weird!

      If I manually edit the date filter, I see that the filter actually starts on 2/16/15. However, 2/28/15 should still be filtered in by this.

      I can manually set the filter to 2/1/15 (in which case it works), but if I move the slider to another month, the date part changes from 1, so the weird excessive filtering happens again.

       

      Any idea what's going on? Is this a Tableau bug or am I doing something wrong?

        • 1. Re: Unexpected MONTH(Date) Filtering
          Yuriy Fal

          Hi David,

           

          I share your confusion, let me explain.

          The MONTH(Date) Green Pill is actually just:

          DATETRUNC('month', [Date])

          So it is effectively gives 2015-01-01 00:00:00

          for every Date in January 2015, and so on.

           

          Date Range Filter allows setting both MIN and MAX values

          at any choosen date (by moving slider controls on a quick filter

          or choosing dates from a calendar when on a Filter shelf).

           

          So it is easy to set say left (MIN) range value at 2015-01-02,

          thus filtering out January 2015 Marks from a view.

           

          Tableau is wise (to a point) allowing user to choose

          from Month/Year values only when on a Slider quick filter.

          But move the left slider control a little bit -- and you loose

          the (lower) month altogether -- until moving to the far left again.

           

          I'm feeling frustrated every time setting a Range Filter

          on an aggregated (truncated actually) Date Green Pill --

          up to the point when I'm almost ready to suggest an Idea of

          an "Intelligent" Date Range Filter, which behaves (on it's both ends)

          just like any Date Parameter set to Range -> with Step Size defined.

           

          And every time I'm trying to resist the urge to share this idea,

          so I'm falling back to a pair of Date Parameters -- and no Slider.

           

          Yours,

          Yuri

          3 of 3 people found this helpful
          • 2. Re: Unexpected MONTH(Date) Filtering
            David Li

            Hi Yuriy, thanks for the quick reply!

             

            I get what you're saying, but I still don't see how DATETRUNC('month', [Date]) could filter out the 2/28/15 value, because that would give 2/1/15 for 2/1/15, 2/16/15, and 2/28/15.

            • 3. Re: Unexpected MONTH(Date) Filtering
              Yuriy Fal

              Hi David,

               

              The logic would be like this:

               

              DATETRUNC('month', #2015-02-28#) = #2015-02-01#

               

              When the filter left boundary is set to say #2015-02-02#,

              then the filter condition for the #2015-02-28# date value

               

              DATETRUNC('month', #2015-02-28#) > #2015-02-02# 

               

              returns FALSE, thus the #2015-02-28# date is filtered out.

               

              Yours,

              Yuri

              2 of 2 people found this helpful
              • 4. Re: Unexpected MONTH(Date) Filtering
                Jonathan Drummey

                Here's the simplest explanation I know of for this behavior: For range filters on continuous dates the *display* is set to the chosen date level like MONTH(Date) in this case but the filter itself is *always* at the day level. That's how the logic the Yuriy accurately described ends up with a filter that can show February on the Quick Filter legend and then 2/16 when we edit the filter.

                 

                Like Yuriy I've also resorted to using parameters or using a Relative Date filter instead of a range filter to avoid this problem. There's nothing like having to explain to a frustrated user that their data isn't actually missing, they just needed to slide the filter control a few more pixels to the left until the invisible magic spot is reached.

                 

                James Baker, you might be interested in this one.

                3 of 3 people found this helpful
                • 5. Re: Unexpected MONTH(Date) Filtering
                  David Li

                  Hi Yuriy and Jonathan, thanks for the clarification! What weird behavior. You would think that Tableau would automatically shift by a month instead of a fixed number of days when it displays MONTH on a slider.

                  • 6. Re: Unexpected MONTH(Date) Filtering
                    James Baker

                    Indeed.  I have brought this design flaw directly to the attention of the appropriate product development team.  Thanks.

                    4 of 5 people found this helpful