6 Replies Latest reply on Jan 29, 2018 5:40 AM by Erwin Erwin

    quarterly increments on date range filter

    Steve Gesuale

      Hi,

       

      After searching the forum, I can't seem to find quite what I'm looking for.

       

      I have data that is quarterly, but the date field is dd-mmm-yy. Each quarter uses the first day of the quarter, e.g., 01-Apr-16

       

      I want a date range filter where the slider is restricted to quarterly level increments, not individual dates. So when adjusting the slider, the user can only choose Q2 2016 and not, for example, 10-Apr-16.

       

      I've tried a Custom Date at quarterly level but that doesn't seem to work.

       

      Is this something really simple that I'm missing?

       

      Thanks for any help!

      Steve

        • 1. Re: quarterly increments on date range filter
          Ivan Young

          Hi Steve,

          Below are the steps I use to build a continuous quarterly date filter.

           

          1.  Drag your date to filters.  Click next, click ok.  Your filter should now be on the shelf as a continuous by exact date.

           

          2. Click on the date field in filters and select Quarter.  If you are showing the filter it may disappear after this step.  Just re-add it.

           

           

          3.  Take a well earned coffee break

           

          Let me know if you have any questions.


          Regards,

          Ivan

          2 of 3 people found this helpful
          • 2. Re: quarterly increments on date range filter
            Steve Gesuale

            Hi Ivan,

             

            Thanks for the response. I also thought that specifying Quarter on the Date pill on the filter shelf would do the trick.

             

            However, I think the issue is that it's still possible for the user to (inadvertently) select a start date w/in a given quarter that actually excludes that quarter's data (since the Date field is the first day of the quarter). Below (and attached) shows how the filter includes 2014 Q3 but the display does not (since the actual start date of the date range is 2-Jul-14 and the date for the data is 1-Jul-14). I can't depend on the users to "get" that the dark line in the date range filter indicates where/when the data is).

             

            Capture.JPG

            So my goal is to have a slider which would prevent the user from choosing a "partial" quarter such that when adjusting the slider only "full" quarter selections are made.

             

            Thanks again - next step: coffee break!
            Steve

            • 3. Re: quarterly increments on date range filter
              Ivan Young

              Hey Steve,

              I see what you are talking about but I've had my coffee and ready to take another stab.  Tableau appears to be passing an exact date even though it is only displaying a quarterly value which I personally find a little lame but I guess it does make sense as it's a continuous date.

               

              Fortunately I think there is a workaround  give this a try.

               

              1.  Create a calculated field [Continuous Discrete Date]:  year([Date]) * 10 + datepart('quarter',[Date]) - This will create a numeric value for each quarter which you will format.

               

              2. Apply a custom number format to [Continuous Discrete Date]:  Default Properties -> Number format  ####-Q#.  This will give your filter a good look.

               

              3.  Drag [Continuous Discrete Date] to filters and set it to continuous.  Select show filter.  I believe this will give you the results you are expecting.

               

               

              Let me know how this works for you.  It seems like there should be an easier solution but I don't know of any.

               

              Regards,
              Ivan

               

              I did a little more testing and while it returns the correct values when you select a year quarter there seems to be values for 2011-Q5-Q9 in the filter which don't exist in the DB.  Although I did work for a company who claimed to have a Q5.

               

              2 of 2 people found this helpful
              • 4. Re: quarterly increments on date range filter
                Steve Gesuale

                Hi Ivan,

                 

                Thanks for the solution! I think I can make that work.

                 

                I'm noticed the same issue w the magical extra quarters so I might play with the calculation and hopefully not break anything.

                 

                I appreciate your help on this one!

                Steve

                • 5. Re: quarterly increments on date range filter
                  Ivan Young

                  Hey Steve,

                  It's my pleasure, I actually learned quite a bit about continuous dates from replying to this post.  Let me know if you find a way to get rid of those pesky extra quarters.

                   

                  Ivan

                  • 6. Re: quarterly increments on date range filter
                    Erwin Erwin

                    Hi Ivan,

                     

                    This is a genius idea! It helps me in creating the customized date filter for clients!

                     

                    For those who have some problem in showing the date as shown below, please right click on the calculated field and convert it to dimension.