3 Replies Latest reply on Feb 22, 2012 7:16 AM by dana.komar

Can a parameter be used to call a variable in a calculated field calculation?

Hello all,

Here is my overall question: Can a chosen parameter value indicate what variable is used in a calculated field calculation?

Here is the situation around my question: I have a sales data aggregated by customer and date (month/year) and fields that have the number of transactions the customer had at each of 7 venues for that month/year. Each venue has its own column, so it is a cross tab of my original transaction data set pivoted on venue. I would like to set up a table that allows the user to select a venue, and then will return the a count of the number of customers that had transactions at that venue and the sum of transactions those customers had at ALL venues. My plan is to use a parameter field to allow the person to choose a venue, then make a calculated field to be used as a filter to keep records that have a non-null value in that venue's value field, then sum up the filtered transactions for all 7 venue value fields and do a distinct count on the customers. So the calculated field to keep records would be:

If Isnull([venue1]) = False Then "Keep" Else "Drop" End

But I want the parameter chosen to stand in for the venue. So if they choose the parameter to be Venue3, the calculated field would be

If Isnull([venue3]) = False Then "Keep" Else "Drop" End

Dana

• 1. Re: Can a parameter be used to call a variable in a calculated field calculation?

Dana, you can do what you want but not exactly the way you're thinking. Parameters can't 'stand-in', but they can do what you want, you just have to spell out each option. Something like this:

CASE [My Parameter]

WHEN 'Venue1' THEN IIF( ISNULL([venue1]) = False, "Keep", "Drop")

WHEN 'Venue2' THEN IIF( ISNULL([venue2]) = False, "Keep", "Drop")

WHEN 'Venue3' THEN IIF( ISNULL([venue3]) = False, "Keep", "Drop")

END

The IIF is a little easier to use than the IF in this case.

--Shawn

• 2. Re: Can a parameter be used to call a variable in a calculated field calculation?

Hi Dana

Is there a reason you have pivoted your data before connecting it to Tableau? YOu'll probably find it easier to have a dimension called "Venue" and will have more control over parameters that way.

Andy

• 3. Re: Can a parameter be used to call a variable in a calculated field calculation?

Thanks Shawn. I was afraid that would be the answer. Thanks for the more efficient code!

Dana