CODEWITHSUNDEEP
tableau-apicalculated-field
Jan Horčička
Jan Horčička

Reputation: 853

Tableau mixing aggregate and non-aggregate results error

I have a problem creating a calculated field in Tableau. I have data like so:

ID    ...   Status     Step1    Step2    Step3
1     ...   Accepted   1        1        1
2     ...   Waiting    1        0        0
3     ...   Discard    0        0        0
4     ...   Waiting    1        1        0
...

I would like to create a calculated column that will give me the name of the last Step, but only when status is 'Accepted'. Otherwise I want the status. The syntax is quite easy, it looks like this:

IF [Status] = 'Accepted' THEN (
    IF [Step3] = 1 THEN 'Step3' ELSEIF [STEP2] = 1 THEN 'Step2' ELSEIF [STEP1] = '1' THEN 'Step1' ELSE 'Step0') 
ELSE [Status]

The problem is that the column 'Status' is a Dimension and the 'Step' statuses come from Measure. So they are AGG(Step1), AGG(Step2),... I guess that is the reason I get this error:

Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions.

I am not very familiar with Tableau. Any idea how I can solve this?

Upvotes: 1

Views: 4897

Answers (2)

Jan Horčička
Jan Horčička

Reputation: 853

Solution:

Just use function ATTR that will make the non-aggregate function (Status) into an aggregate one. Then it is possible to combine them and the calculation is working.

IF ATTR([Status]) = 'Accepted' THEN (
    IF [Step3] = 1 THEN 'Step3' ELSEIF [STEP2] = 1 THEN 'Step2' ELSEIF [STEP1] = '1' THEN 'Step1' ELSE 'Step0') 
ELSE ATTR([Status])

Upvotes: 3

Sam M
Sam M

Reputation: 4166

Tableau automatically interprets numeric values as measures. It appears though that in your case they are a boolean (0 for false, 1 for true) and really ought to be dimensions.

Convert Step 1, Step 2, and Step 3 to dimensions. Highlight the fields, right click, and choose Convert to Dimension.

Upvotes: 0

Related Questions