6 Replies Latest reply on Mar 10, 2011 3:56 PM by Joe Mako

    How do I quick filter for multiple result combinations?

    Catherine Rivier

      Hi everyone,

      I was hoping to tap into the expertise on here to help me add a usability feature in a workbook I'm developing.  I pushed the workbook in its current form (with dummy data) to Public:

      http://public.tableausoftware.com/views/TestData_2/Sheet1?:embed=yes&:toolbar=yes&:tabs=no

       

      The data I'm using contains results, if they exist, for individual files for each of a set of named "queries" (here they are state names, in the field QueryName).  If a result exists, it has both a Score (>0) and an Offset (>0) - if not, the record isn't present.

       

      What this workbook is designed to do is allow the user to see the individual files, and click on links to those files.  Right now I can select individual QueryNames, but the functionality I want is this:  The user needs to have an additional filter checkbox, where they can find files that only have results for multiple selected queries.

       

      For example, I want to see only files that have results (score >0) for Hawaii AND Idaho.  Then I want to see only those with results for Idaho AND Michigan AND Minnesota.

       

      Any ideas?

        • 1. Re: How do I quick filter for multiple result combinations?
          Richard Leeke

          This question is very similar to another one posted the same day, here: http://www.tableausoftware.com/support/forum/topic/quick-filters-and-rather-or.

           

          I posted one way of tackling that one on that thread - I think the same approach will work.  I think it should be possible to add a parameter which switches between "any of the selections" and "all of the selections".  I'll try to have a quick go with your dummy data if I get a chance.

          • 2. Re: How do I quick filter for multiple result combinations?
            Richard Leeke

            Well that was a lot harder than I expected.  I had all sorts of unexpected Tableau crashes: out of memory errors, and just plain weird table calculation behaviour, but I eventually got it going based on what I'd done in that other thread.

             

            Along the way I exchanged a few emails with Joe Mako, who pointed out a much simpler way of counting the selections (which works unless you are using an MS JET data source - i.e. anything except a text file, Excel, or Access).  So I've included two sheets, one using my horrible convoluted table calculations and one using Joe's simple approach.  I'd go with Joe's approach - thanks Joe.  ;-)

             

            The thing I really struggled with for a while was that once I'd introduced the table calculation to work out whether the file had all queries, the cross-tab insisted on displaying blank cells for all possible permutations of dimensions.  I'm fairly sure this is because of Tableau doing something referred to as "padding the domain", which in this case was being "overly helpful".  I eventually suppressed the blank rows with an extra condition in one of the calculated fields to make sure there really were underlying rows for each cell.  That's what this is for, in the [Filter Condition] calculated field:

             

            ... AND (SUM([Number of Records])>0)

             

            Have a careful look at all of the calculated fields in the attached workbook, paying particular attention to the table calculation "Advanced" settings: the definition of the partitioning and sorting.  Post a follow-up if you need some more explanation.

            • 3. Re: How do I quick filter for multiple result combinations?
              Joe Mako

              Here is is another alternative for this case:

               

              Create a parameter, call it "Show", with the values:

               

              Has Any
              
               Has All
              


               

              And then a calculated field like:

               

              CASE [Show]
              
               WHEN "Has Any" THEN 0
              WHEN "Has All" THEN WINDOW_COUNT(SUM([Score]))-SIZE()
              END<.code>
              
              for this calc field, you can set the default table calc partitioning, the compute using to "QueryName".
              
              Then place this calc field on the level of detail shelf, and then ctrl-drag it to the filter shelf, and keep only when it is zero.
              
              attached is an example workbook.
              
              (Richard, In the workbook you attached, I did not see any difference in the sheets, and I do not think the "jm" fields would have worked correctly)
              
              Also, the table calc that I used is properly handling the NULL values, instead of need another calc field to filter them. If "Score" is NULL, then then SUM of that NULL value is NULL, and the COUNT function will not count a null value.
              
              • 4. Re: How do I quick filter for multiple result combinations?
                Richard Leeke

                Good catches, Joe, sounds like your version is much better (I'm not on a PC with Tableau at the moment).

                • 5. Re: How do I quick filter for multiple result combinations?
                  Catherine Rivier

                  This is a fantastic solution.  The final version is deceptively simple, but it works like a charm on my real data too.

                   

                  I'm learning still exactly how all of these solutions worked, but thanks for all of your help!

                  • 6. Re: How do I quick filter for multiple result combinations?
                    Joe Mako

                    I'll add this my list of video tutorials to make.