4 Replies Latest reply on Aug 16, 2018 1:42 PM by Brent Johnson

    Filter on dates on or between two dates

    REBECCA LANE

      I have a list of programs with a start date and end date.  I want to create a filter (relative filter like this quarter, last quarter, etc) but instead of using the program start date and filtering on that i want it to show the data if it falls between or on the two dates.  For example, if i use the filter i have created in the attached twbx file of Begin date = this quarter then it only shows me the programs that actually have the begin date of this quarter.  I want it to show anything that is active this quarter (meaning the duration is active within this quarter).  Trying to create an 'in market' visualization to show what is actively running during the selected timeframe - regardless of when it begins or ends.

      (using Tableau Desktop 10.2.1)

       

      Any help is much appreciated!


      Thanks,

       

       

      Becky

        • 1. Re: Filter on dates on or between two dates
          John Sobczak

          I saw a good example of something like this at the Tableau Conference hands on training where you do a join with your table and a simple generic date table.  Here is the training example workbook.

          • 2. Re: Filter on dates on or between two dates
            Kris Rhodes

            Hi Becky,

             

            I'm going to be focusing on this part here, because that sounds like what you're trying to do is central around this portion:

            Trying to create an 'in market' visualization to show what is actively running during the selected timeframe - regardless of when it begins or ends.

             

            To do this, I determined a baseline (Minimum start date of all the programs).

            Baseline Minimum: {MIN([Program Begin Date])}

             

            I created a parameter representing the date I want to compare against.

            Comparison date: 9/5/2017

             

            I also created a String List parameter representing the different kind of comparisons I could do.  In this case, I assumed you wanted to compare against Year, Quarter and Month; more granular than that didn't seem like it would be valuable.  Really important: The value of year, quarter, month must be all lowercase.  The display can be uppercase.

            2017-02-23_9-52-44.png

             

            I created three calculations to determine the difference between the baseline and the program start, program end, and comparison date, using the "Relative to" as the comparison granularity.  What this means is if you had your earliest program start in 2017 Q2 and your comparison date was in 2017 Q3, a "Relative to: quarter" comparison would return a difference of "1 quarter".

             

            General function: DATEDIFF(comparison_granularity, start date, end date)

            Baseline vs Comparison Date: DATEDIFF([Relative to],[Baseline Minimum], [Comparison date])

            Baseline vs Program End: DATEDIFF([Relative to],[Baseline Minimum], [Program End Date])

            Baseline vs Program Start: DATEDIFF([Relative to],[Baseline Minimum], [Program Begin Date])

             

            Now that I have the relative differences, I can compute whether something falls within the same year, quarter or month of the comparison date.

            Baseline comparison: [Baseline vs Comparison Date] <= [Baseline vs Program End] AND [Baseline vs Comparison Date] >= [Baseline vs Program Start]

             

            Drag that to your filter shelf, filter on true values only and you end up with something that looks like this: (comparison date is 9/5/2017, relative to quarter - Program 4901's end date falls inside this quarter so it's included in the viz))

            2017-02-23_9-52-43.png

             

            Hopefully this solves your problem.

             

            Side note:

            While I only used one date in my comparison, this will work for one or two parameters specifying the start or end date.  You'd have to create an additional [Baseline vs. Comparison Date] calculation and modify the [Baseline comparison] calculation, but it would still work.

             

            If you're trying to do it against today's date instead of some specific date ("As a user, I want to see the currently active programs), you can use the functions TODAY() or NOW() instead of [Comparison Date] in your calculations.  There's some differences between the two so if you do end up going down this route please refer to the Date function help file:

            1 of 1 people found this helpful
            • 3. Re: Filter on dates on or between two dates
              Brent Johnson

              Kris Rhodes, I'm trying to do something similar to this. Can you elaborate on your Side note for how to do this to show any active programs within 2 date parameters?

              • 4. Re: Filter on dates on or between two dates
                Brent Johnson

                For anyone who stumbles on this in the future, I figured out a way to use 2 parameters (Active Start Date and Active End Date) to allow a user to select "active" records between a given time period. So if there are a bunch of records with Start and End Dates, the use case would be a user who wants to filter the list to include records that were active at the Start and End Dates and anytime in between. After making the parameters, I used the following True/False Calculated Field and then put it as a Filter:

                 

                ([Active End Date]>=[Active Start Date]) AND

                (([Start Date] <= [Active Start Date] AND [End Date] >= [Active Start Date])

                OR

                ([Start Date] >= [Active Start Date] AND [End Date] <= [Active End Date])

                OR

                ([Start Date] <= [Active End Date] AND [End Date] >= [Active End Date]))

                 

                The 1st part ensures that the End Date parameter occurs after the Start Date parameter. The 2nd part picks up any records "active" on the Active Start Date Parameter. The 3rd part picks up any records that began and ended between the Parameter Dates and the last part picks up "active" records on the Active End Date Parameter.