1 2 Previous Next 16 Replies Latest reply on May 10, 2016 1:44 PM by Michael Hesser

    Using one filter to search multiple parameters simultaneously

    Taylor Woodroof

      In the table below we have 4 different Parameters to search. As you can see, some of the line items share values with other line items.

       

      I need to be able to search 1, 2, 3, or all 4 of the different parameters at one time.

       

      Currently the "calculation" I have accounts for every possible scenario of parameter combinations, i.e.   IF PatientID is searched AND Prescriber is searched, then Show, ELSE Hide.  If PatientID and Date and Prescriber are searched, then Show, ELSE Hide. IF Date is searched, then show, ELSE, hide. and so on. This calculation is currently working, but if I want to add additional parameters, such as Patient Name, or Medication Name, then my list of possible parameter combinations will soon grow unwieldy. Is there a way to create a smarter and more compact calculation to account for all possible combinations of these parameters?

       

       

      Patient ID

      Prescription Number

      PrescriberDate
      11111Smith1-1-16
      12222Jones1-2-16
      23333Smith1-2-16
      34444Hunt1-3-16
        • 1. Re: Using one filter to search multiple parameters simultaneously
          Joe Oppelt

          Need to understand more.

           

          When your user wants to search for "Smith", and also wants to search for 1-2-16, are you treating these as an "OR" operation?  (In my example, 3 rows would come up if it were an "OR" operation -- those that satisfy "Smith" and those that satisfy 1-2-16.)

           

          If you are treating this as an "AND" operation, only one row would come up.

           

          If you are doing "AND", why not simply let the user select his Date values in one filter, and select his Prescriber values in another filter, and let Tableau work as it is designed to work?

          • 2. Re: Using one filter to search multiple parameters simultaneously
            Joe Oppelt

            Just a point of nomenclature.  Parameters are a specific type of tool in Tableau.  I think you're just talking about columns, and more accurately, dimensions.

            • 3. Re: Using one filter to search multiple parameters simultaneously
              Taylor Woodroof

              When more than one parameter is searched at the same time, it would be treated as "AND."

              The idea is that if an individual wants to search all prescriptions of Dr. Smith, then he/she would input "Smith" and not search the other parameters, but if you want to search all prescriptions for Dr. Smith on 1/1/16, you would then be searching two parameters to narrow your results- "Smith" AND "1/1/16."

              • 4. Re: Using one filter to search multiple parameters simultaneously
                Joe Oppelt

                OK.  So why not just give the user the different filters and let him pick what he wants?  It's a standard Tableau design.


                See attached.  (It's an 8.2 workbook, but it'll upgrade to any version you are on.)

                 

                See Sheet 3.  I just created a simple sheet.  I added two filters:  Category and Continent.  By default they are set to ALL.  The user can pick what he wants for Category, and separately if he wants, pick which continent(s) to include.

                 

                You would do the same for the various dimensions you want your user to select in your workbook.

                • 5. Re: Using one filter to search multiple parameters simultaneously
                  Joe Oppelt

                  Maybe I'm missing what you are trying to do.

                   

                   

                  Do you know how to upload a workbook?  If I had your workbook, you could show me in your own example what you expect the user to do, and what you need Tableau to do on the sheet in response to that.

                  • 6. Re: Using one filter to search multiple parameters simultaneously
                    Taylor Woodroof

                    Thanks for your help so far, Joe.

                    I am not able to upload my workbook because it contains protected health information.

                    I see your example workbook and notice that the two filters you are using allow for checking and unchecking boxes to hide and show data. I am needing to search and narrow my dataset using free-text Parameter Control "Cards", all 6 of which feed their input (i.e. "Smith" and "1/1/16") into 1 dimension calculation I have created. That dimension calculation is the only filter I currently am using.

                    • 7. Re: Using one filter to search multiple parameters simultaneously
                      Joe Oppelt

                      I can't wrap my head around what you are describing.

                       

                      If you can't use your actual data, hack up something that represents what you are doing.

                       

                      Consider the steps in this video:  Anonymize your Tableau Package Data for Sharing

                      • 8. Re: Using one filter to search multiple parameters simultaneously
                        Taylor Woodroof

                        Hey, Joe,

                         

                        I recreated a simplified version of my workbook as best as I could, but unfortunately the DATEPARSE function is not available because this dummy workbook is not connected to my SQL warehouse, which means several of my calculations do not work and are showing a red ! .

                         

                        The dimension entitled "CALCULATION" contains the primary script that makes the real workbook function, and which in my real workbook I am using as the only Filter.

                        • 9. Re: Using one filter to search multiple parameters simultaneously
                          Joe Oppelt

                          What is dateparse trying to do here?  (For MedStopDate, for example?)  Are you looking to have the user type in a date?  If so, why not make that parameter a date field?  Then MedStopSate can simple be this:

                           

                          [MedicationStopDate].

                           

                          Even if we just do this for the example workbook to get us past the dateparse issue.

                           

                          Again:  I'm trying to understand what you want the user to do, and what you want Tableau to do in response to that.

                           

                          I can help you here, but I need to know what's supposed to happen.  You are immersed in this application.  I have never seen it before.

                          • 10. Re: Using one filter to search multiple parameters simultaneously
                            Zach Leber

                            Here are another couple ideas:

                             

                            1. multi-field wildcard search

                            https://community.tableau.com/thread/117053

                             

                            2. multi-field parameter search

                            a) concatenate your dimensions together with a delimiter (the date formatting needs more work here)

                            [Delimiter]+STR([Patient ID])+[Delimiter]+STR([Prescription Number])+[Delimiter]
                            +STR([Prescriber])+[Delimiter]+STR([Date])+[Delimiter]

                            b) create a boolean field to determine if there is an exact and complete match

                            LEN([Search Parameter])=0
                            OR CONTAINS([Search String],[Delimiter]+[Search Parameter]+[Delimiter])

                            multi-field parameter search.png

                            • 11. Re: Using one filter to search multiple parameters simultaneously
                              Joe Oppelt

                              Aha.


                              I'm beginning to understand what you have here.

                               

                              So you want users to type in free-form (no selection values) into each of the parameters, and it looks like not all parameters have to be filled in, and your CALCULATION calc is making heads-or-tails of each of those possible combinations.  It's not just a yes-or-no decision on whether a value is checked.

                               

                              And, not knowing all the valid permutations, it looks like some of the entries for a given parameter are valid only when another parameter is set to certain things...

                               

                              And I see why you are using string values for the date parameters -- because you want to allow blank values, but a date parameter will always default to something (and if you don't put something in the default, Tableau forces 12/30/2899)...

                               

                              Am I getting close?

                              • 12. Re: Using one filter to search multiple parameters simultaneously
                                Taylor Woodroof

                                The ultimate application is for an external website to query this Tableau Workbook via a URL (once it is published to our Tableau server) and then that website will show the search result.

                                For instance, a simplified example URL is something like -  www.tableau.com/MRN=1&StopDate=1/1/2016.

                                 

                                For that to work, my 6 parameters in the workbook need to be searchable by free-text string, because that is what the end user will be inputting into the external website. So, changing the [MedicationStopDate] to date format would not align with the goal for this application.

                                 

                                JOE-

                                Yes, your latest response is a solid explanation. My current workbook works as intended, BUT if I wanted to add a 7th search parameter, or 8th, then I would have to add that variable to all of the 50+ lines of calculation in the CALCULATION field. There has to be a smarter and simpler way to perform the same kind of search that will more easily allow for the addition of more parameters.

                                 

                                ZACH-

                                Thanks for the input.

                                • 13. Re: Using one filter to search multiple parameters simultaneously
                                  Joe Oppelt

                                  Looking at how those combinations have to fit together, it appears you do have to spell it out like you are doing.  "Sometimes 'this' can be null.  Sometimes 'that' can be null..."

                                   

                                  Have you thought about working this from the "Hide" side of the equation?  You have identified 50+ combinations that result in "Show".  Maybe it's easier to say what should NOT be shown rather than what should be shown.

                                   

                                  Is there a pattern to all this?  Can ALL values be set?  If one is null, must some other one be set?


                                  What I am leading to is this.  Maybe you could come up with a mathematical way to address this.  Set a calc of 1 or 0 for each one.  (Sum them up if all cannot be set.  If SUM = 6, you have "Hide", for example.)  Or create a binary function of the possible combinations.  (000010 = 2;  000101 = 5; etc.) and create a list of values that result in "Show" or "Hide".

                                   

                                  Just some ideas to consider.

                                   

                                  Bottom line, given all the combinations (the pattern for which I did not spot with a cursory look at them), you'll need to evaluate them one way or another.  And yes, when you add a 7th and an 8th, the number of combinations are going to increase way more than just linearly.  .

                                  • 14. Re: Using one filter to search multiple parameters simultaneously
                                    Joe Oppelt

                                    And, for the record, I see where you are going with the URL concept.  And that's a sharp use of the product, in my opinion.  I've used that sort of thing, sort of in reverse.  I have taken all the parameter values and filter settings in a dashboard, and strung together a URL for a hyperlink action to open a special crosstab sheet that was published, and it results in the user getting an excel dump of his current settings, in a format that *I* choose rather than Tableau chooses when he wants to view data.

                                    1 2 Previous Next