6 Replies Latest reply on Oct 14, 2016 12:22 PM by Brian Malek

    Having Overlapping Results on a Filter/Calculation

    Brian Malek

      Hello everyone! I have tried looking around and cannot quite seem to get around a particular problem...

       

      I am using a database connection to join a couple tables and show some data on a dashboard. In one of the tables there is a creation date field that I would like to setup a filter on with the following options: today, last 7 days, and last 30 days. Based on my research and results it appears that Tableau does not allow for overlapping results - meaning that the data cannot be in both the "today" bucket and the "last 7 days" bucket.

       

      I can see this via an if statement in a calculated field:

      IF DATEDIFF("day",[Creation Date (Ps Rc Case)],NOW()) < 1 THEN

          "Today"

      ELSEIF DATEDIFF("day",[Creation Date (Ps Rc Case)],NOW()) < 7 THEN

          "Last 7 Days"

      ELSEIF DATEDIFF("day",[Creation Date (Ps Rc Case)],NOW()) < 30 THEN

          "Last 30 Days"

      ELSE

          "Older"

      END

       

      Using this formula data from today shows up in the "Today" bucket (creation date of 10/14), but does not show up in the "Last 7 Days" bucket (only contains creation dates of 10/8 through 10/13).

       

      Is there a way to do what I am looking for?

        • 1. Re: Having Overlapping Results on a Filter/Calculation
          Wes Reneau

          Hi Brian,

           

          You are correct when you stated that data cannot exist in multiple buckets. Once a condition is met, that data is essentially 'spoken for'. The way I have done this in the past, is creating multiple calculated fields, one for today, one for last 7 days, etc...

           

          -Wes

          • 2. Re: Having Overlapping Results on a Filter/Calculation
            Brian Malek

            Hi Wes and thanks for the quick reply.

             

            I thought about that path, but was not sure how to setup a filter to show the three calculated fields as the options to choose from. I could add each one as its own filter, but that would cause issues if they do not unselect the other filter...  I'm a little new to the Tableau so I have not encountered a situation where I had to try to build a filter outside of a single calculation.

             

            Do you create another calculated field to allow for filtering in your scenario or do you have some actions setup to make sure they can only select one of the filters?

             

            Thanks!

            • 3. Re: Having Overlapping Results on a Filter/Calculation
              Wes Reneau

              I can think of two options.

              1) Create 3 calculated fields and show them all. Quick filters will continue to work.

              2) Create a parameter and allow the user to select one of the time dimensions.

               

              I'm working on a example workbook and will post it shortly.

              1 of 1 people found this helpful
              • 4. Re: Having Overlapping Results on a Filter/Calculation
                Kaz Shakir

                My suggestion would be to use a parameter and a calculated field.  The parameter would be a string - list that would contain the values you mentioned.

                Then create a calculated field that will determine if the record is in the category selected by the parameter, maybe something like this:

                ShowRecord

                CASE [AgeCreated]

                    WHEN "Today" THEN

                        IIF(DATEDIFF("day",[CreatedDate],NOW()) < 1,1,0)

                    WHEN "Last 7 Days" THEN

                        IIF(DATEDIFF("day",[CreatedDate],NOW()) < 7,1,0)

                    WHEN "Last 30 Days" THEN

                        IIF(DATEDIFF("day",[CreatedDate],NOW()) < 30,1,0)

                    WHEN "Older" THEN

                        IIF(DATEDIFF("day",[CreatedDate],NOW()) >= 30,1,0)

                END

                 

                This will create a calculated column that will have a value of either 1 or 0 depending on the age of the record, and the parameter value selected.  Be sure to convert the "ShowRecord" field to a Dimension rather than a Measure.

                 

                Then, You place the "ShowRecord" field on your filter shelf, and select the "1" value only.

                 

                And then show the parameter controls that will allow you to change the value of the parameter and the records in your viz should change accordingly.

                 

                Let us know if that addresses your objective.

                1 of 1 people found this helpful
                • 5. Re: Having Overlapping Results on a Filter/Calculation
                  Wes Reneau

                  Brian,

                   

                  Since the Superstore data is not current I replaced Last 7 Days and 30 Days with calculations for 2013 and 2014. Same concept, I just expanded the ranges to work with the sample data. I think example number 2 is what you are looking for.

                   

                  Attached you'll see two views:

                  1) Quick Filter Example with 2 Calc Fields

                          The calculated fields are named WR - 2013 an WR - 2014. As you will see, I added the Order Date quick filter and Region quick filter. Each calculated field still honors the quick filters selections.

                    

                  2) Parameter Example

                      For this one I created a parameter and named it WR - Select Time Dimension with the following list of values.

                  I then created a calculated field named WR - Profit (Using Parameter) and placed it on the filters shelf with a value of 'T'.  This checks that the parameter value matches and that the order date equals a specified date or range. If both the conditions are met then the field = 'T' and the corresponding Profit record will display.

                   

                  if [WR - Select Time Dimension] = 'Today'

                      and [Order Date] = TODAY()

                          then 'T'

                  ELSEIF [WR - Select Time Dimension] = 'Year 2013'

                      and [Order Date] >= DATE("2013-01-01")

                      and [Order Date] < DATE("2014-01-01")

                          then 'T'

                  ELSEIF [WR - Select Time Dimension] = 'Year 2014'

                      and [Order Date] >= DATE("2014-01-01")

                      and [Order Date] < DATE("2015-01-01")

                          then 'T'

                  ELSEIF [WR - Select Time Dimension] = 'All'

                          then 'T'

                  else 'F' end

                   

                  You'll noticed that if you select Today within the parameter, the view is blank. That is because there are NO records with an order date of today.

                   

                  Take a look at the workbook and let me know if it's the correct answer or helpful or you have additional questions.

                   

                  -Wes

                  • 6. Re: Having Overlapping Results on a Filter/Calculation
                    Brian Malek

                    That's fantastic, works like a charm - thanks so much!