It sounds like you need a case statement, something like this:
CASE [Select Interest:]
WHEN 'Golf' THEN [Golf]
WHEN 'Dining' THEN [Dining]
Where [Select Interest:] is the parameter.
Thanks for the reply. When I try this, I get a calculation error:
Cannont use 'boolean' type in CASE expression
Essentially this means that you have boolean (T/F) calculations after the THEN. Probably your calc is something like this:
Yes? Then change it to this:
IF [Interest]='Golf' THEN [Interest] END
If that doesn't do it you'll need to post a packaged workbook so we can see what's going on.
How is your case statement written?
Here is a packaged workbook with sample data. Basically I was trying to use my parameter "Interest" to help select so that filter became"TRUE" and unchecks "FALSE".
Let me know if that makes sense?
Sampletestbook1.twbx 1.1 MB
Thanks Shawn. See attached workbook below.
Thanks for the workbook. Here's the calc.
IF [Interest]='Golf' AND [Golf]=TRUE THEN 1
ELSEIF [Interest]='Casinos' AND [Casinos]=TRUE THEN 1
ELSEIF [Interest]='Dining and Nightlife' AND [Dining and Nightlife]=TRUE THEN 1
ELSEIF [Interest]='All' THEN 1
ELSE 0 END
Thanks Shawn!! That works beautifully!
For my personal enrichment, could you explain a bit how it works?
Sure. IF and CASE statements execute top to bottom one condition at a time, and as soon as a TRUE value is reached it falls out jumping to the END, and in this case out of the calc. IF/THEN statements take the form of:
IF (a TRUE/FALSE condition) THEN (a result -- string, number, date, etc.) END
The boolean error you ran across (one of the most common) is a bit misleading, because you actually need to have a boolean statement in an IF statement. You just need these statements to be before the THEN.
To get your stuff to filter the way we want, I had to use two conditional statement. The first tests whether the parameter is set to a specific value. The second is the one doing the actual filtering, Golf=True. [Note most of the time we filter using the filter shelf, but we can also filter within a calculation. This is a very useful technique.]
Finally by using the AND connector we are requiring both conditions to be met. It wouldn't work using OR because it would fall out on the first line no matter what the parameter was set to. The ALL option doesn't filter out anything because there isn't a second condition.
Play the execution in your head and watch where it falls out given different combinations of conditions. Just remember there is only one 'line' that will be executed each time this query is run.