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?
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.
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."
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.
superstore -- example.twbx 989.6 KB
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.
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.
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
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.
May 10 Test.twbx 56.4 KB
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:
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.
Here are another couple ideas:
1. multi-field wildcard search
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]
b) create a boolean field to determine if there is an exact and complete match
OR CONTAINS([Search String],[Delimiter]+[Search Parameter]+[Delimiter])
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?
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.
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.
Thanks for the input.
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. .
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.