6 Replies Latest reply on Nov 16, 2016 7:47 AM by Sherzodbek Ibragimov

# Help with Calculated Field (mix aggregate and non-aggregate)

Good afternoon,

I have a report in Tableau connected directly to our datawarehouse, and the report is in the format of a typical pivot table (like what you would see in Excel).

It shows data as such:

Column A (ID number)    Column B (ID name)    ...     Column Z (count distinct of activity performed)

My task is to create a calculated field that will be a new column, and will "label" rows of data based on the following conditions:

- If column A is a certain number, column B is a certain name, and the number (an aggregated count distinct, since its similar to a pivot table) is greater than or equal to a certain threshold.

So far my code is as follows:

IF [column Z] >= 20 THEN "label"

ELSEIF [column Z] >= 10 AND  ([column a]='90' OR [column a]='91') THEN "label"

ELSEIF [column Z] >= 10 AND  (CONTAINS([column b],'name1') OR  CONTAINS([column b],'name2')

OR CONTAINS([column b],'name3') OR CONTAINS([column b],'name4')

OR CONTAINS([column b],'name5') OR CONTAINS([column b],'name6')

OR CONTAINS([column b],'name7') OR CONTAINS([column b],'name8'))

THEN "label"

END

The issue is that Tableau is giving me an error on this calculated field, saying "cannot mix aggregate and non-aggregate arguments with this function" and underlining the AND condition. It seems that tableau does not let me use column Z and the other columns in the code because it sees that column Z is an aggregate field while the other fields are just facts.

- Is there any workaround to this?

• ###### 1. Re: Help with Calculated Field (mix aggregate and non-aggregate)

Hi Nelson,

I had this problem before. But easy fix is to use ATTR, an aggregation for dis aggregated fields. Please read this thread: Resolving "Cannot mix aggregate and non-aggregate arguments" Calculation Error | Tableau Software . Thus, you most likely use ATTR for your ID name as it can not be aggregated. Let me know if it doesn't work.

• ###### 2. Re: Help with Calculated Field (mix aggregate and non-aggregate)

I modified the code as follows and got nulls everywhere for this new calculated field.

- Is there anything I should change, or is there another way?

IF  >= 20 THEN "label"

ELSEIF  >= 10 AND  (ATTR()='90' OR ATTR()='91') THEN "label"

ELSEIF  >= 10 AND  (CONTAINS(ATTR(),'name1') OR  CONTAINS(ATTR(),'name2')

OR CONTAINS(ATTR(),'name3') OR CONTAINS(ATTR(),'name4')

OR CONTAINS(ATTR(),'name5') OR CONTAINS(ATTR(),'name6')

OR CONTAINS(ATTR(),'name7') OR CONTAINS(ATTR(),'name8'))

THEN "label"

END

Thanks,

Nelson Santana

• ###### 3. Re: Help with Calculated Field (mix aggregate and non-aggregate)

ATTR should be placed in front of each your non aggregate field. So, try following formula and see if works. I am not sure of your formula itself.

IF [column Z] >= 20 THEN "label"

ELSEIF [column Z] >= 10 AND  ([column a]='90' OR [column a]='91') THEN "label"

ELSEIF [column Z] >= 10 AND  ATTR(CONTAINS([column b],'name1')) OR  ATTR(CONTAINS([column b],'name2'))

OR ATTR(CONTAINS([column b],'name3')) OR ATTR(CONTAINS([column b],'name4'))

OR ATTR(CONTAINS([column b],'name5')) OR ATTR(CONTAINS([column b],'name6'))

OR ATTR(CONTAINS([column b],'name7') OR CONTAINS([column b],'name8')))

• ###### 4. Re: Help with Calculated Field (mix aggregate and non-aggregate)

If you can give me your sample in excel, I can try myself. Can you do that? Also, I didn't copy all of your formula in my calculated filed.

• ###### 5. Re: Help with Calculated Field (mix aggregate and non-aggregate)

if sum([Sales])<15000 then 'Low'

elseif sum([Sales])>100000 and CONTAINS( attr([Sub-Category]),'es') then

'Good'

else ' '

end

• ###### 6. Re: Help with Calculated Field (mix aggregate and non-aggregate)

Did any of above solution helped to solve your problem? If so, please mark one of them as correct answer so it is cleared from questions that need to answered and other can benefit from it. If not, please feel free to ask. Thanks

Sherzod