1 2 3 Previous Next 41 Replies Latest reply on Jul 17, 2013 8:16 AM by Jim Wahl

    Multiple ways to filter on a date field

    Jackie Klein

      Hi,

      I have users who want to filter the date by the way I have it in the attached (select a Parameter - Day, Month, Quarter, Year), then the Date based on the Parameter.  Then I have another set of users who want to use a slider to filter by the same date field so that they enter their own start and end dates (they may want to see only a partial month, quarter, or year, etc.).

       

      Is there a way to have multiple ways to filter by a Date?  I am wondering if/how I can accomplish this request.

       


        • 1. Re: Multiple ways to filter on a date field
          Jim Wahl

          Hi Jackie,

           

          There are a couple of ways you might do this. The main approach, I think, is to add a parameter that allow your users to select a "custom period range" or whatever you want to call it.

           

          Then you could either

          1) Create a separate worksheet that is filtered by order date and "swap" that view into the dashboard when selected.

          or

          2) Modify your date filter calculation with an IF/THEN and filter the Order Date differently.

           

          I'm not sure which is cleaner, and it may depend a bit on your users and use case. (1) has the advantage of a nice slider range date filter and (2) eliminated the need to show/hide a worksheet on the dashboard.

           

          I did (1) in the attached. To get this dashboard, I

          1. Added a boolean parameter "Use custom period range?"

          2. Duplicated your Worksheet.

          3. In the original worksheet I edited the PERIOD_REPORT date with a conditional on Use custom period range. If true, PERIOD_REPORT is null and this worksheet will disappear.

          4. In the duplicate worksheet, substituted Order date for PERIOD_REPORT.

          5. Duplicated category, added it to the filter shelf and added a conditional filter on this for Use custom period range. This will make this worksheet disappear when Use customer period range = False.

          6. Added a horizontal container to the dashboard and put both worksheets in this container.

          7. Added a field to the header that shows the date range in the view.

           

          Again, I'm not 100% happy with this solution, but I think option (2) might require some perhaps confusing parameter naming---i.e., you don't get the nice date slider.

           

          Jim

          • 2. Re: Multiple ways to filter on a date field
            Jackie Klein

            Thanks Jim,

            I tried to recreate your Option 1 in my live workbook which is using data blending between SQL Server and Tableau server data sources.  I get an error msg: Tableau Data Engine Error: 4: ambiguous function call (day unknown), 2 possibile resolutions

             

            Then I tried just doing it with one data source - SQL Server and I still get the error.  Does this not work with data sources that are not Excel?

             

            Also, I see a calculated field in your sample file - Sales Order Date PERIOD Filter.  But you don't reference that in the instructions, so is that a field that you do not use? 

            • 3. Re: Multiple ways to filter on a date field
              Jim Wahl

              HI Jackie,

               

              I suspect the error is caused by the field for the date range header.

               

              If it works when you remove this field then I'd add it again, but double check the table compute using -- you may need to use an Advanced compute using to addtrss all dimensions in the view.

               

              Perhaps also your date field  / format is causing problems.

               

              Please let me kfind the resolution interested.

               

              JIm

               

              Oh, and I didn't use the Sales PERiOD field.

              • 4. Re: Multiple ways to filter on a date field
                Jackie Klein

                Hi Jim,

                It does not work when I remoe the Date range header field.  Did you try this using a SQL Server data source?  Curious if that works for you?

                • 5. Re: Multiple ways to filter on a date field
                  Jackie Klein

                  Sorry jim - i think I missed the part where I need to create a condition on the Duplicated Category.  How do a create a condition on a filter?  I can't see how to do this. 

                  • 6. Re: Multiple ways to filter on a date field
                    Jackie Klein

                    Nevermind - just googled it.  I will give that a try and see if that fixes the problem. 

                    • 7. Re: Multiple ways to filter on a date field
                      Jackie Klein

                      Hi Jim,

                      Could you do me a favor and test your Option 1 with a SQL Server connection and let me know if it works?  I created a condition on the Duplicated Cateogry and that did not solve the problem  It appears the problem is with the "Worksheet" tab (original view), as the "Worksheet Custom Date Range" works fine.

                       

                      Thanks much,
                      Jackie

                      • 8. Re: Multiple ways to filter on a date field
                        Jackie Klein

                        Hi Jim,

                        Just curious if you think you will be able to help test this in SQL Server?  Otherwise, I might post as a new discussion to see if someone else can? 

                        • 9. Re: Multiple ways to filter on a date field
                          Jim Wahl

                          Hi Jackie,

                           

                          Sorry for the delayed response. I'm away from my computer until Thursday.

                           

                          I'm happy to take a look then, but go ahead and repost if you want / need a faster reply.

                           

                          Jim

                          • 10. Re: Multiple ways to filter on a date field
                            Jackie Klein

                            Hi Jim, I can't wait.  Enjoy your holiday though!  Thanks!

                            • 11. Re: Multiple ways to filter on a date field
                              Jackie Klein

                              Sorry I meant to say I CAN wait.  Sorry Thanks!

                              • 12. Re: Re: Multiple ways to filter on a date field
                                Jim Wahl

                                Hi Jackie,

                                 

                                Everything seems to work the same as Excel when I use either SQL Server 2012 (11.00) and SQL Server 2008 R2 (10.5).

                                 

                                I also noticed that I had an extra worksheet that used the continuous (green pill) PERIOD_REPORT date dimension as a filter. When switching to a custom date range, this caused an error message relating to domain padding. I've removed this sheet in the attached V2.

                                 

                                Also in V2, I cleaned up a few things:

                                1. Removed unused fields PERIOD_REPORT (copy) and Sales Order Date PERIOD Filter
                                2. On both worksheets, I edited the category filter box to reset the title (Edit title > reset) and show all values in database.
                                3. I re-added these sheets to a new dashboard and re-organized the filters. Since Category is a global filter, only one copy of that quick filter box is needed.
                                4. Changed the title on the order date quick filter to "Custom period range"


                                Hope this helps.


                                Jim

                                • 13. Re: Re: Multiple ways to filter on a date field
                                  Jackie Klein

                                  Thanks Jim.  So it appears my issue with SQL is happening only when I am using an extract of the SQL data.  When I remove the extract I no longer get the error.  Does this same thing happen for you?

                                  • 14. Re: Re: Re: Multiple ways to filter on a date field
                                    Jim Wahl

                                    Interesting. I got the same error when I created an extract from SQLServer.

                                     

                                    It's caused by the IF [Use custom period range?] THEN NULL  in the PERIOD_REPORT calculated field. I was using this to make this worksheet disappear when [Use custom period range?] is true. But you can accomplish the same thing with the Category (copy) method I used in the custom period worksheet.

                                     

                                    Here's what I did in the attached v3:

                                    - Removed the IF THEN NULL from PERIOD_REPORT (should now look like your original calc).

                                    - Added Category (copy) to the filter shelf in Worksheet and specified the condition for the filter as "NOT [Use custom period range?]"

                                    - Edited Date PERIOD per table calc, since the comma  was in the wrong place: May, 1 2010 instead of May 1, 2010.

                                     

                                    Hope this works for you too. ...

                                     

                                    Jim

                                    1 2 3 Previous Next