Andy, I took another glance at it, and I believe the source of the frustration is the AGG() pill you have on your filter shelf.
To get the results you want, and the filter you want, you will need a different calculation.
Instead of what I recommend earlier, just change the formula for "% satisfied" from:
sum([Answer (1 or 0)])/count([Answer (1 or 0)])
IF COUNTD([Person Number])>=10 THEN SUM([Answer (1 or 0)])/COUNT([Answer (1 or 0)]) END
Note: (sorry for the poor advice earlier, what I recommended before: "-(SIZE()-TOTAL(COUNTD([College])))" should not be in your rank/Index calculation)
studentbarometer_edit_2.twbx 301.4 KB
Great stuff Joe, that works perfectly. This technique is really powerful. I've added a parameter to the workbook such that the user can switch between % satisfaction or rank as they wish. This kind of thing could be applied to any survey data where you've got multiple questions and a bunch of respondent types.
Regarding why the calc field needs the countd check: I assume that's because the Table Calculation is done before the filtering? Therefore, the ranks are calculated and then the values with fewer than 10 respondents are removed.
The reason why the check for "COUNTD([Person Number])>=1" needs to be done in the calculated field instead of on the Filter shelf with an AGG() pill is because of sorting. Sorting for the table calculation is done before the AGG() filter is applied. So for that "Welcome" question, where there a bunch of colleges that did not meet that filter requirement, it had a bunch of null values introduced because of that filter, but the sort order did not change, so the ranking calculation saw a null value as the previous for colleges and the logic says if the previous is null, then return "1".
This is why my question back on http://www.tableausoftware.com/blog/six-education-day#comments is important. If you do not know the order of operations, then you can get into situations like this where it is not clear what is going on and why you are not getting the results that you expect. I do not believe the answer provided in that comment thread to my question is complete. I do not know what the full answer is, but I know some pairs, like sorting for table calcs happens before AGGG() filters are applied.
I guess this leads to a question for Tableau developers: should table calc sorting happen before AGG() filters are applied?
This is great stuff.
I wonder is there a way that the table (or chart - as I have used these calculations to display in bar format) will update automatically?
When I change between questions, the dimension order stays the same and does not update to reflect the order of who is ranked 1, 2 etc
It's OK - I figured it out using computed sorts!
Be aware that the method I demonstrated above is not the best way in my current opinion to accomplish this. See Re: How do I calculate a field for each day (that is repeated for each item)? for another example.
In short, if you use create a Set of the dimensions in use, and place this Set pill on the level of detail shelf, it removes the need for all the extra table calc stuff in this formula:
IF NOT ISNULL([% satisfied]) THEN IIF([% satisfied] == LOOKUP([% satisfied], -1),PREVIOUS_VALUE(1), INDEX(), 1) END
and you can go back to using just
(both use an advanced compute using)
The Set is removing the padding that Tableau adds to the data before evaluating the table calcs. sometimes this data padding is desirable, eg forecasting, and other times, like this ranking situation, it is not desired.
Also you can use table calcs to visually sort the display of data, and if you want a table calc sorted by the result of a table calc, eg a raking of an aggregate of table calc results, one option is to use a self-joined data set.
attached is an example.
studentbarometer_edit_3.twbx 318.1 KB