2 Replies Latest reply on Dec 11, 2018 8:57 AM by Jennifer Hysuick

    Calculated Field when the contents of the IF statement are not mutually exclusive

    Jennifer Hysuick

      I know I can do this with separate calculated fields, but unfortunately doing it that way limits my options for what else I want to do.  I cannot post a sample workbook because it is too time consuming to anonymize the data at this point.

       

      I have a dataset of students who are enrolled in various programs, and there are separate columns for Gender, Indigenous, Visible Minority and Disability (equity demographics). In my original worksheet, I have a Calculated field for each of these columns - one for the total count, and one for the % of total. The viz shows the % of total.


      What I am trying to do is something like this:

      IF [Gender Desc] = 'Female' THEN "Female"

      ELSEIF [Indigenous] = 'Indigenous' THEN "Indigenous"

      ELSEIF [Visible Minority] = 'Visible Minority' THEN "Visible Minority"

      ELSEIF [Disability] = 'Disabled' THEN "Disability"

      END

       

      Unfortunately the numbers are not coming up correctly for any of them when I put CNT(ID) - and I can only assume that it is because a student could be in each of those categories (not mutually exclusive).  I see that my IF statement is not the way to go on this, and I am hoping for an alternative.

       

      The reason for this line of thinking is that when I have the separate calculations for each demographic piece, I cannot seem to add a tool tip that will show the counts - it shows the counts for each calculation on every item, rather than the one specific to the row (does that make sense?). I'm adding  a screen shot of how it shows the Total Female number for each of the items in my vis:

       

       

      IS there a better/proper/easier way to do what I hoping to accomplish? I'm not familiar with Sets - but what I have found is that I can't use more than one field in it.

       

      This is what I'm trying to do (with the above calculation being used):

       

       

       

      But the numbers are not correct (the Indigenous count is actually over 3500).

       

      Any ideas?