11 Replies Latest reply on Oct 19, 2012 7:10 AM by Shawn Wallwork

    Creating Compound Filters

    Christopher Henderson

      Hi All,

       

      I currently have a set of data that lists the skill set of over 100 nurses, overall there are 10 possible skills. So for example a nurse may have only 2 of the ten skills or may infact have all 10. My challenge is around creating a compound filter or some other technique that would allow me to filter on nurses that have a combination of skills.

       

      For example, I want to select a nurse that has skill 1 and skill 8, currently using the basic filters the nurse would appear if I selected skill 1 or 8. Essentially I am looking to create a condition whereby the user specifies the combination of skills required and the relevant nurses appear.

       

      Is there any way of doing this?

       

      Thanks

       

      Chris

        • 1. Re: Creating Compound Filters
          Russell Christopher

          Christopher -

           

          (Great name, by the way) Have you considered using Sets to do this work? You could create multiple sets, each which define a different "set" of skills. You'd then just drag the set you care about to the Filter shelf and be ready to go.

           

          Let me know what you think.

          • 2. Re: Creating Compound Filters
            Christopher Henderson

            Hi Russell,

             

            Thanks for your response, problem is that there are too many possible variations on the number of skills a nurse could have. I am bit stuck on how to get around this...

             

            Cheers

             

            Chris

            • 3. Re: Creating Compound Filters
              Russell Christopher

              Hmm - not really sure what to tell you here - If you decide to replace the current "Filter on Skill" Quick Filter with something else, you're going to have to do some "manual definition" of some sort regardless.

               

              Whatever technique you choose, if there are a lot of combinations that need to be defined, you have a lot of work to do defining them.

               

              Maybe I don't understand your scenario well enough? Let's pretend that Tableau is magic and can automatically add features to itself to allow you to do whatever you wanted. If that were the case, explain the approach that you might take...that may fill in the blanks for me a bit.

              • 4. Re: Creating Compound Filters
                Christopher Henderson

                Thanks Russell,

                 

                So the output of the report I am working on is a map of the locations of all of our nurses in the UK. Each nurse has a number of skills there are a potential number of 10. What the users need to be able to do is select the skills that they require through either a filter or parameter. The map will then pin point where there are nurses with that combination of skills.

                 

                For example I am looking for a nurse who is trained to work with children and can administer antibiotics (2 skills). I want to be able to select these 2 skills and for the map to show me where the nurses are that have this combination of skills.

                 

                I hope this makes sense...

                 

                Cheers

                 

                Chris

                • 5. Re: Creating Compound Filters
                  Russell Christopher

                  It does!

                   

                  Based on your scenario, I'd think a pretty basic Quick Filter on "Skills" displayed as a Multiple Value List would work nicely - select any combination of :"skill check boxes", and the map filters.

                   

                  However, I believe you've considered this approach and rejected it -- which makes me think I'm still missing something important.

                   

                  Edit: Never mind - you need to AND the skills, not OR them. Give me a minute

                   

                  Message was edited by: Russell Christopher

                  • 6. Re: Creating Compound Filters
                    Russell Christopher

                    What shape is your data?

                     

                    Does it look like this?:

                     

                    Nurse1, Skill1, New York

                    Nurse1, Skill2, New York

                    Nurse1, Skill3, New York

                    Nurse2, Skill1, Boston

                    Nurse2, Skill4, Boston

                    Nurse2, Skill73, Boston

                     

                    ....or does it look more like this:

                     

                    Nurse1, Skill1, Skill2, Skill3, New York

                    Nurse2, Skill1, Skill5, Skill73, Boston

                    • 7. Re: Creating Compound Filters
                      Christopher Henderson

                      Hi Russell,

                       

                      My data looks like:

                       

                      Nurse1, Skill1, New York

                      Nurse1, Skill2, New York

                      Nurse1, Skill3, New York

                      Nurse2, Skill1, Boston

                      Nurse2, Skill4, Boston

                      Nurse2, Skill73, Boston

                       

                      Thanks

                       

                      Chris

                      • 8. Re: Creating Compound Filters
                        Russell Christopher

                        OK - the shape of your data makes things difficult, but I got something. It's not for the faint of heart. Hopefully someone else will have a different/brilliant idea that simplifies this a bit. You also might want to consider "pivoting" the shape of the data so that each skill is a column with a Y/N value - doing so could make this problem easier to solve.

                         

                        Anyway, please review the attached sample:

                         

                        Overview:

                         

                        A list of our 6 nurses, where they live, and what skills they have. Each nurse only has 3 skills instead of your 10.

                         

                        Warm Up:

                         

                        1. Note the Parameter Skills Needed which allows one to pre-select HOW many skills you are about to look for
                        2. Note Skill filter which allows people to choose the skills
                        3. Examine Sum of Skills pill on Rows shelf. It is based on an expression which counts the total number of rows being displayed for each nurse - Each row signifies a skill for that nurse which matches one of the selections in the Skills filter
                        4. Examine the Chooser pill on the Rows shelf. It compares the number of rows per nurse (skills per nurse) and sees if that number matches the value entered into the Skills Needed parameter. We see "Candidate" or "Lacks Skills" depending on whether the Nurse meets the criteria or not.

                         

                        Now, Hide Things

                         

                        This is essentially the same report as above, except we've turned "Chooser" into a filter, filtering out Nurses without a Chooser="Candidate" value. We're also hiding the Skills and Sum of Skills row values.

                         

                        Map it

                         

                        We convert the previous report into a Map.

                        1 of 1 people found this helpful
                        • 9. Re: Creating Compound Filters
                          Russell Christopher

                          ...and another solution which manages to get this done with a single expression (created by a colleague who is brilliant at this sort of thing).

                           

                          FYI - On Friday we often send out a tricky "Viz Challenge" for our pre-sales and professional services consultants to challenge themselves with. Your scenario will get 15 minutes of fame later today.

                          • 10. Re: Creating Compound Filters
                            Christopher Henderson

                            Great! Many thanks for you help with this one, I have coined the name Compound Filters if it ever gets added to Tableau as a function! I will have a team of happy nurses which is always a bonus!

                             

                            I look forward to the responses later on.

                             

                            Cheers

                             

                            Chris

                            • 11. Re: Creating Compound Filters
                              Shawn Wallwork

                              Russell, I'd been working on this off and on since it was posted. It certainly was trickier than it looked at first. The COUNTD() was the missing link that didn't occur to me. Thank your brilliant colleague for this.

                               

                              --Shawn