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.
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?
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.
TableauSample.twbx 1.2 MB