5 Replies Latest reply on Oct 13, 2015 12:21 PM by david.cooke.0

Can I use a calculated field to determine if a value is part of a set?

I haven't seen anything addressing this on the forums, so maybe someone has some ideas.

Let's say I have data like the following:

(id,category,value)

(1,a,40000)

(2,a,42000)

(3,b,45000)

(4,c,23000)

(5,d,50000)

And I have a set based on either category or value (e.g. Set A = category is in {a,c}; or Set B = id has sum(value) >= 45000).

Is there a way I can test for membership within a set in a calculated field.  For example, I am looking to do something like this

AdjustedValue = if [category] is in [Set A] then [value] * [SetAMultiplier] else [value] end

or

SetBCategories = if [id] is in [Set B] then  [Category] else null

I know I could just place the set defining logic within the calculated fields, but this doesn't help if I need to change the set members or criteria, especially if I had many calculated fields with to update whenever the set logic changed.

Thanks,

Zac

• 1. Re: Can I use a calculated field to determine if a value is part of a set?

It seems that you're concerned with having reusability in the logic that defines a set and if that logic changes, the change would persist throughout other calcs related to the set.  One approach might be to create a boolean calc for each set/group condition.  For example

SetA Calc:(Set defining logic)

[Value] >= 45000 and [Category] = 'B'

In your above example you would have:    if [SetA Calc] then value*[SetAMultiplier] else [value] end

Instead of changing the logic in all the calcs related to SetA you would just change the SetA Calc anytime the definition of that set changes.

Keep in mind aggregating values in the set condition will complicate things because you are then forcing the calcs to be related to a specific level of detail.

Just a suggestion, not sure if it applies to your use case.

• 2. Re: Can I use a calculated field to determine if a value is part of a set?

Zac,

How about using the new "sets in calculation" capability of Tableau 8?

Let's Talk About Sets | Tableau Software

Pedro

• 3. Re: Can I use a calculated field to determine if a value is part of a set?

Didn't realized this worked until now:

I have a set called "SetA"

In a calculation it acts as a boolean.

if [SetA] then [Sales]*2 else [Sales] end 1 of 1 people found this helpful
• 4. Re: Can I use a calculated field to determine if a value is part of a set?

Thanks. I've overlooked that new feature.  I think this will help out siginificantly.

• 5. Re: Can I use a calculated field to determine if a value is part of a set?

Given a set S, named 'setS' in a Tableau workbook, an occurrence of the name of the set ([setS]) in a formula is converted on evaluation for any row R to a boolean value (true/false) indicating whether or not R is a member of S. If the formula for a calculated column contains only a set name, then the column is classified as a dimension and cannot be converted to a measure. A formula to count the members of S is IIF([setS],1,0)