Tableau mixing aggregate and non-aggregate results error

Question

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?

Hi, what's the format of your [step3] measure? Assuming it is a number, try changing it to a string, does this remove the error? — Ben P
Hey, is this copy and pasted from the actual code? With the step if statement, you seem to be saying if step 3 & 2 is equal to number one, then on step 1 you are saying if its equal to a string ("1"). Does changing this fix your issue? — lampbob
Can you show the definition of Step1? Is it a calculated field or an explicit field in your data set — Alex Blakemore
You might find it easier to work with multiple data sources -- assuming they are all views of the same underlying tables, created by different joins in the data pane. So one data source that has a row per ID, useful for answering questions such as what is the current status of each item. And another data source that has arow per status change (e.g. recording that at this date, item #3 changed moved to Step 2 ) That data source would be convenient for other types of questions, say those focused on tracking status changes over time — Alex Blakemore

Jan Horčička Jan Horčička · Accepted Answer · 2017-10-24T07:36:43

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])

Tableau mixing aggregate and non-aggregate results error

2 Answers