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"



      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?



      Thanks in advance for your help, I really appreciate it!