6 Replies Latest reply on May 14, 2014 11:23 AM by Keith Helfrich

    Quick Filters - AND rather than OR

    . Matthew

      I'm having something of a mental block here.  What I want to do is to use a filter, preferably a quick filter with check boxes to provide an AND solution rather than the usual OR solution.

       

      E.G. If you take Superstore data and plot Sales by Customer Name, filter this by Customer State:Conneticut to restrict the amount of data and then add a quick filter using Product Sub Category.  From this check Office Machines and Rubber Bands and you will be left with 12 customer names.

       

      These customers have bought either Office Machines OR Rubber Bands.  What I'm after is a filter that will give me the 3 customers out of these 12 that have bought both Office Machines AND Rubber Bands.

       

      This not the same as the  choice between Include vs Exclude filters.  It is an additive and increasingly exclusive filter.

       

      Any ideas?

        • 1. Re: Quick Filters - AND rather than OR
          Richard Leeke

          Well I've managed to come up with something that works (though there is a little quirk) - but it certainly wasn't obvious.  It's a cool trick, though.  Maybe someone else can come up with an easier way?

           

          This is what I did.

           

          1) I set up the sheet as per your example, with quick filters on State and a sub-Category.

           

          2) I defined Table Calculations to work out the overall number of distinct Sub-Categories in the data selected by the filters and also the number of distinct Sub-Categories for each Customer.  I used the TCRL Distinct Count Calculation which I suggested a few weeks ago for these fields.

           

          3) I defined another Table Calculation which returns TRUE or FALSE depending whether or not the Customer has the same number of Sub-Categories as the overall selection.

           

          4) I defined a filter on that last calculated field, selecting only rows for which the value = TRUE.

           

           

          The quirk I referred to is that this only works if all selected Sub-Categories exist within the selected State(s).  If you select a Sub-Category which doesn't exist in the chosen State, the overall count will be lower, so the result will actually be all rows for Customers who have all selected Sub-Categories which exist in the chosen State.  I don't think there are any examples like this in the Superstore data.

           

          What I tried to do to get around this was to define the filter on Sub-Category so that it shows "Only Relevant Values".  That would mean that you can't select Sub-Categories that don't exist in the chosen State.  But when I selected that, the quick filter dialog didn't show any Sub-Categories at all (see screen shot).  This looks like a bug.

          • 2. Re: Quick Filters - AND rather than OR
            . Matthew

            Richard, this is very neat and it does exactly what I was looking for thanks.  It's actually a surpirisingly useful analysis and I can't imagine why I haven't tried to use it before.

             

            What I have realised as a result of looking at you handiwork, is just how critical the sequence of events is when building table calcs, as I managed to break the sheet multiple times when I first started to play around with it. 

             

            Does someone know of any reading/examples that might help turn my current tactics of trial and error into something more logical?

             

            Incidentally I had exactly the same problem as you reported when trying use 'relevant values' in the secondary quick filter.

            • 3. Re: Quick Filters - AND rather than OR
              Joe Mako

              Matthew,

               

              I am slowly building a library of short tutorial videos about Tableau calculations and other combination of features in Tableau at http://joemako.tumblr.com/

               

              I welcome your feedback and recommendations for content to cover.

              • 4. Re: Quick Filters - AND rather than OR
                . Matthew

                Joe, the videos are spot on - good topics and the right length too. 

                 

                Any chance you could you post the .twbx up there as an accompanyment to future videos? 

                 

                I'm sure I speak for a few mere mortals out here who would definitely benefit from the opportunity to do some forensic examination of your work.

                 

                BTW was it your dog I heard trying to get in on the act? I guess he was just warning you about a missing END statement! 8-)

                • 5. Re: Quick Filters - AND rather than OR
                  Joe Mako

                  Yes, that was my dog :)

                   

                  These videos are me learning how to make tutorial videos, so I plan to get a real microphone and do some editing instead of a just a single take.

                   

                  For the videos that are there, if you follow the link to the forum post, you can find the workbooks that I used. In the future, after a redesign and re-record of the content, I will keep the videos and packaged workbooks close to each other.

                   

                  Thank you very much for your feedback.

                  • 6. Re: Quick Filters - AND rather than OR
                    Keith Helfrich

                    Hello, I'm curious.  Has anyone made new progress on this ?  The work-around above is kludgy & doesn't solve all use cases.

                     

                    I've submitted the new idea:

                     

                    Multi-Select Filters: INTERSECTION instead of UNION

                     

                     

                     

                    Thanks!

                    Keith Helfrich | LinkedIn

                    Keith Helfrich | Twitter