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

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

    nsant013

      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?

       

       

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