2 Replies Latest reply on Apr 10, 2012 1:14 PM by Cole Pacak

    Replicating an IF and COUNTIF Excel function to determine ethnicity in Tableau

    Cole Pacak

      In a table, I have five columns: ID, Hispanic, Asian, Black, and White. The ID column contains integers, and each one of the ethnicity columns has a "Y" or "N". It looks like this:

       

      A: IDB: HispanicC: AsianD: BlackE: White

      1

      YYYY
      2YNNY
      3NYNY
      4NYNN
      5YNNY
      6NNYY
      7YNYY
      8YNNY
      9NYNY
      10NNYN

       

      Don't ask why, but the state where I live uses the following criteria to determine ethnicity:

      -If "Y" in [Hispanic], then "Hispanic"

      -If there is more than one "Y" in [Asian], [Black], and/or [White], then "Two or More Races"

       

      After all that:

      -If not "Hispanic" or "Two or More Races" and "Y" in [Asian], then "Asian"

      Ditto for [Black] and [White]

       

      As a result, in Excel I used the following formula:

      =IF(Hispanic="Y","Hispanic",IF(COUNTIF(C2:E2,"Y")>1,"Two or More Races",IF(Asian="Y","Asian",IF(Black="Y","Black",IF(White="Y","White")))))

       

      Can I replicate this function as a calculated field in Tableau?

       

      Thanks