8 Replies Latest reply on Mar 7, 2012 7:25 AM by adamomaisano

    range of dates

    . nibav

      I am having a date column and I am setting it as a range of dates filter and it shows me 6/1/1999 12:00:0 - 1/1/2010 11:59:5.  Actually I want to show only the month and year like 6/1999, 7/1999 etc... I am trying to create a calcuated field and setting it as range of dates.  Is there a way to do this?

        • 1. Re: range of dates
          James Baker

          Where exactly is "it shows me" happening?  In the quickfilter card, or in your data table?

           

          If your column is a Datetime instead of a Date, then there's a bit of magic regarding Tableau's best guess as to when it should show times in the quickfilter.  You'll want to set both times to 12:00:00 AM and then save, close, and reopen your workbook to make the times go away.  Then you'll want to set your Default Date Format (right-click on dimension name, look for Set Default) for that column to be mm/yyyy.

          • 2. Re: range of dates
            . nibav

            well, "it shows me" is happening when I drag a date column to set it as a filter.  It pops a window where we can choose the options and then I select the date column to "Range of Dates" option in the filter and then click OK.  I solved it by unchecking the show times option in the filter.

             

            But my question was - Is it possible to create mm/yy in the slider("Range of dates")?

            • 3. Re: range of dates
              James Baker

              It should be, if I understand you correctly.  Thus this instruction: "set your Default Date Format (right-click on dimension name, look for Set Default) for that column to be mm/yyyy".  That formatting should be reflected in the display of the slider quickfilter.

              • 4. Re: range of dates
                . nibav

                ok, I will try this and will let you know.

                • 5. Re: range of dates
                  . nibav

                  James,

                   

                  I tried as you suggested.

                   

                  1. Changed the date column -> change data type -> Date

                  2. Changed the date column -> Set Default -> Custom - mm,yyyy

                  3. Dragged and Dropped as filter -> Range of dates -> Quick filter

                   

                  Now it displays as 01/06,02/06,03/06 ......12/2010.  When I am setting the filter from 01/2009 to 01/2010 and looking at the query generated by Tableau the filter looks like below:  Since we have only month end dates like 1/31/2009,2/28/2009 etc... the first condition would work but the second condition wouldn't bring in Jan 2010 data. 

                   

                  WHERE ((((("datemmyydd") >= TIMESTAMP'2009-01-11 00:00:00')

                  AND (("datemmyydd") <= TIMESTAMP'2010-01-14 23:59:59')))

                   

                  How to fix this?

                  • 6. Re: range of dates
                    James Baker

                    Hmm.  I guess the slider is still operating at full precision, rather than truncating its dates to the month level.  That's a little sad, but understandable.

                     

                    Your dates are at the end of the month, so that looks right to me.  Are you saying that you want both ends of the filter range to be inclusive, so 01/2009 to 01/2010 would be 13 months instead of 12?

                     

                    I can't think of a way to do that with a continuous field.  You may have to fall back to a checkbox list on MY(date) - at least you can multiselect a range of checkboxes.

                    • 7. Re: range of dates
                      guest contributor

                      I am having a date column and I am setting it as a range of dates filter; my data set is always last 30 days of data. Everything works fine the way I created but when I refresh the date range moves in the data but my filter still show old values. Is there a way to refresh the filter when the data refreshes to show the right dates.

                      • 8. Re: range of dates

                        Had the same request - a $date dimension that I wanted a ranged quick filter on, truncated to Month/Year rather than day-level dates.

                         

                        James' suggestion worked for me on 6.1 and 7.0: right-clicking the dimension and setting the field properties > date format... to the level of granularity desired.

                         

                        Thanks!