6 Replies Latest reply on Jun 6, 2016 6:11 AM by Joseph Sutkowski

    How to Filter Events that Occurred within All or Part of a Time Range Using a Single Parameter

    Joseph Sutkowski

      Hi all!

       

       

      What I want to do:

       

      I want to create a dashboard that uses a Month Selector to filter for events that either occurred within all or part of a month.

       

      Example: Stephanie acted in a commercial from August 2nd to September 15th, so selecting either August or September will include Stephanie's data.

       

       

      What I've tried:

       

       

       

      What I've attached:

       

      • Excel file with raw data
      • Workbook with sample chart

       

       

      Alternatively, I know I can use two filters - one for the start month and one for the end month - but want to try this out first.

       

      Let me know if any of you beautiful, brainy people have questions!

       

      jojo

        • 1. Re: How to Filter Events that Occurred within All or Part of a Time Range Using a Single Parameter
          james.diaz

          is your data source an excel file?

           

          with custom sql you can try filtering with a date parameter where its between the 1st of the start date month and the last of the end date month.

          • 2. Re: How to Filter Events that Occurred within All or Part of a Time Range Using a Single Parameter
            Joseph Sutkowski

            Hi James,

             

            In my actual project, I'm using custom SQL. However, in this example I'm using an Excel file as my data source.

             

            Would you be willing to show how to implement that type of parameter?

             

            Best,

             

            Joe

            • 3. Re: How to Filter Events that Occurred within All or Part of a Time Range Using a Single Parameter
              Ivan Young

              Hi Joe,

              Here's is what I did to implement a solution to return the results you are looking for.

               

              1. Create your date parameter.  You may need to update this from time to time.

               

              1.  Create two new fields for start and end date to be used in the filter

                   a. Start Date for Filter: DATETRUNC('month',[Start Date])

                    b. End Date for Filter: DATEADD('month',1,DATETRUNC('month',[End Date]))

               

              3. Create your filter field.  Date Filter: IF [Date Param] >= [Start Date for Filter] AND [Date Param] < [End Date] THEN 'Show' ELSE 'Hide' END

               

              4.  Drag Date Filter to filters and set to 'Show'

               

              That should do the trick.  Let me know if you have any questions or would like me to upload a 9.2 workbook.

               

              Best,
              Ivan

               

              • 4. Re: How to Filter Events that Occurred within All or Part of a Time Range Using a Single Parameter
                Joseph Sutkowski

                Hi Ivan,

                 

                This solution looks magical (like the vast majority of your work that I've seen) ! .. And yet, I'm struggling to implement it fully. If you don't mind, I think posting the workbook could be super helpful for my noob brain!

                 

                Also, can this type of solution account for events that span beyond 2 months? EG Janice decides to extend her workshop from October to next January?

                 

                Thank you so-so much again!

                 

                Best,

                 

                Joe

                • 5. Re: How to Filter Events that Occurred within All or Part of a Time Range Using a Single Parameter
                  Ivan Young

                  Hi Joe,

                  I've attached a copy of the workbook and this solution will work for date ranges spanning several months or even years.  Basically I am returning all the records where the parameter falls between the start and end dates.

                   

                   

                  The challenge with your data is you want the parameter at the month/year level and your dates are at the day/month/level.  So what I did with the calculated fields was to make the start date fall on the first of the month it is in using DATETRUNC, so 5/3/2016 would be converted to 5/1/2016.  For the end date I set it to the first of the month then as well but then added one month so 6/4/2016 would be converted to 7/1/2016.  We now have fields that will work well with a parameter set to the 1st of each month.

                   

                   

                  For example if I have a record with Start Date of 5/1/2016 and an End Date of 7/1/2016 parameter values of 5/1/2016 or 6/1/2016 will return 'Show' using the Calculated date filter

                   

                   

                  The formula for the filter is:  IF [Date Param] >= [Start Date for Filter] AND [Date Param] < [End Date for Filter] THEN 'Show' ELSE 'Hide' END

                   

                   

                  With the start and end dates plugged into the formula: IF [Date Param] >=5/1/2016 AND [Date Param] < 7/1/2016 THEN 'Show' ELSE 'Hide' END

                   

                   

                  With the Date Param set to 5/1/2016:  IF 5/1/2016 >=5/1/2016 AND 5/1/2016 < 7/1/2016 THEN 'Show' ELSE 'Hide' END  will return true.

                   

                   

                  I noticed that the formula for step 3 in my first reply is incorrect.  It should be IF [Date Param] >= [Start Date for Filter] AND [Date Param] < [End Date for Filter] THEN 'Show' ELSE 'Hide' END.  One other thing is that when you drag the filter to filters the param will need to be set to a value that will return 'show' in order to set the filter to include 'show'  So the parameter shouldn't be set to June when applying the filter as 'hide' will be the only value.

                   

                  Let me know if you have any questions or issues.

                   

                  Best,
                  Ivan

                  1 of 1 people found this helpful
                  • 6. Re: How to Filter Events that Occurred within All or Part of a Time Range Using a Single Parameter
                    Joseph Sutkowski

                    Ivan, you are an absolute Tab-bro with Tableau!

                     

                    I think your explanation above really helped my understanding too! Sweet lanta I hope I can one day answer one of your questions!

                     

                    til then, live long and viz / prosper!

                     

                    Jojo