CODEWITHSUNDEEP
tableau-api
Alan
Alan

Reputation: 31

Tableau: Aggregation of Boolean Conditions (encoded as Strings)

How do you aggregate strings in Tableau? For example, at the detail level I have values of Yes and No but at the aggregated level I only want to show one of those values

For example, if the aggregation has a No in it then Show No otherwise show Yes.

i.e. if the level of detail contains a No then show no otherwise show Yes.

Upvotes: 2

Views: 3481

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11921

It would be a tad simpler if you used Boolean values instead of strings - also that would enforce that there were only two legal values. If your field, say it is called Completed, contained Boolean values (I.e. either TRUE or FALSE), then to show whether all records were completed, I.e. had the value TRUE in the Completed Column, you would simply use MIN([Completed]).

This works because Tableau treats True as greater than False, so MIN(condition) is true if and only if condition is True for every record - ignoring nulls. MAX(condition) is true if and only if there is at least one record with condition set to True. So for Booleans, you can read MIN() as “every” and MAX() as “any”.

The one wrinkle is if your boolean field allows null values. If so, you can decide between a few options. You can take the default behavior which is to silently ignore nulls as if they don’t exist, or you can wrap the field reference in a call to IFNULL() to supply a default value of your choice to replace the nulls. Really the same for any data type and aggregation function.

This technique is useful in several situations, including defining conditions for sets.

Finally, if your data set has to use strings such as “YES” and “NO” instead of booleans, you can convert to booleans easily by defining a new calculated field such as Completed as [Completed-Original] = “YES”

Upvotes: 2

Related Questions