3 Replies Latest reply on Jul 25, 2016 3:54 PM by Joe Oppelt

    Separate data so each person is only counted in one category once

    Kristie Wirth

      Hi all,

       

      I'm working on a student dataset. The dataset has three columns - student ID, academic period, and whether they were pell eligible each semester. Each ID is listed in 1+ rows, depending on how many semesters they were enrolled in school.

       

      If a student was pell eligible (has a Y) in 1 or more semesters, I want to put them in a group of 'Pell Eligible' and group everyone else into a group that is 'Not Pell Eligible'. (I attached a sample of my dataset).

       

      I want to create a table in Tableau that allows me to count the distinct number of students in each category. The problem I'm having right now is that if I use a simple count distinct, students who were pell eligible some semesters but not others are being counted in both categories, while I want them to only be counted once in the 'Pell Eligible' category. Any thoughts on how to do this? Thanks!!

        • 1. Re: Separate data so each person is only counted in one category once
          Joe Oppelt

          If you were to set a calc, row-by-row, that says if the student was pell-eligible that semester, I would set it to 1 or 0.  Then if the SUM([Pell Eligible]) for that student was > 0, then at least one semester he was pell-eligible.

          • 2. Re: Separate data so each person is only counted in one category once
            Kristie Wirth

            So, I get how I could set it up with student ID in rows and have the sum of pell eligible as 0's and 1's to indicate each student!

             

            However, I want to have a table that looks something like this:

             

            Pell Eligible Students 200

            Not Pell Eligible Students 300

             

            How would I set that up exactly?

            • 3. Re: Separate data so each person is only counted in one category once
              Joe Oppelt

              I hacked up something quickly.  There may have been a more elegant way but this is one way.

               

              See attached,.  (I did this in 8.2 because I don't know what version you  need.  It will upgrade to anything.)

               

              So in Sheet 1 I created a calc to determine if a student has any PELL semesters.  [Pell or Not].

               

              So now you have all your students sorted by category.

               

              In sheet 2 I created a table calc to count up all the PELL students.   Then I created a [Pell-or-not-count] calc that sets itself to one or the other value (count of Pells, or total count minus count of pells.)

               

              You'll notice a filter  on Sheet 2.  If you take it off, you'll see that there is a row for each student.  You only want to see one value per category.  So the filter gets the index value that is the last in "Pell" and the first in "Not".  On this sheet you can right click the [Random ID] pill on ROWS and turn off "show header" so that you don't see a student ID on the sheet.  (Note:  This calc works because I sorted [Pell or not] so that Pell comes out first.  By default Tableau wants to put "Not" first because it's alphabetically first.  If I left "Not" at the top, I would want to create a [Not Pell] calc, or monkey with  the filter so that it grabs the index value for the last "not pell" and the index for that one +1 .)

               

              You could also do some of this with LODs if you are using 9.0 or higher.  But this will work fine in 9.0 using the table calcs like this.