CODEWITHSUNDEEP
tableau-api
Tarzan
Tarzan

Reputation: 4538

How do I resolve Tableau calculation error when using OR clause and multiple fields?

I am using Tableau to visualize some data and I am creating a set for every row that meets the following conditionavg.

[Parent Id]=1 or [Account Id]=1

The formula seems straight forward. However, I get an error message saying that "The formula must be an aggregate calculation or refer only to this field." How can I resolve this? Thanks.

Upvotes: 1

Views: 7940

Answers (2)

Tarzan
Tarzan

Reputation: 4538

The solution was to wrap the parentId with a max function.

Max([Parent Id])=1 or [Account Id]=1

Upvotes: 2

Alex Blakemore
Alex Blakemore

Reputation: 11896

If you want to separate individual data rows into two classes according to the value of a boolean expression involving multiple fields (without using aggregation), then make a calculated field from your formula and use that field as a filter or dimension in the view.

If instead you want to make dynamic set based on a specific field, say [Customer Id], and include or exclude [Customer Id]s from the set depending on whether a condition holds, then the condition must obey the restrictions mentioned in your error message.

The goal of a dynamically computed set based on the [Customer Id] field is to unambiguously partition the universe of [Customer Id]s into those [Customer Ids] that are members of the set and those that are not members of the set.

The requirements mentioned in the error message make sense if you think about how sets are likely implemented for relational data sources. For example, Tableau can generate a having clause when aggregate calcs are provided, such as:

select [Customer Id] group by [Customer Id] having min([Parent Id]) = 1 or Sum([Sales]) > 1000

or use a where clause when no aggregate functions are used if the condition only mentions the field defining the set, such as : 

 select distinct [Customer Id] where [Customer Id] < 10

I'm not saying this exact SQL is generated by every driver, but reasoning through what SQL Tableau might generate to implement a feature often sheds light on how it behaves and why there are particular restrictions.

Say you attempted to define a set based on a [Customer Id] field but supplied a formula that didn't fit these restrictions. It would be possible for the formula to evaluate to true for some of the data rows for a [Customer Id] and to evaluate to false for other data rows with the same [Customer Id]. In that case, whether the [Customer Id] was a member of the set or not would be undefined. Set membership would depend on the data row in question.

In that case, I'd say you probably are not really defining a dynamic set based on the Customer Id field, but have some other goal in mind. Which leads back to using a calculated field instead of a set here.

Bottom line: The restrictions in the error message are required for a set based on a field to be well defined, and are not an artificial limitation imposed by an implementation.

Upvotes: 2

Related Questions