Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Combining Small measures into "All Other" category with mnimal manual intervention
Oftentimes I'll have a lengty list of unique values (say Doctor Names) and I want to show a brief summary of the top 20 doctors perhaps, even though there are 200 unique doctors in the data. What I presently do is just add a condition on the Doctor field that makes reference to the COUNT OF RECORDS field, where COUNT OF RECORDS > 35 perhaps (I manually tweak thsi number to get the desired results) so that 20 results show.
But doing this takes a bit of fiddling with the numbers to get the right final number (20), and further, applying this filter excludes the other 180 doctor records, naturally. But I would ideally instead like to show a final 21st entry in the same table that shows the other records aggregated together, so the user can see the contribution these top 20 doctors make in the context of the full total, not just a subset.
I know instead of filtering the data, I could add an ad hoc group that puts the remaining 180 doctors into an "ALL OTHER" group, but that takes significant effort to pick and choose the records to accomplish that.
Any ideas?
Subscribe to the comments on "Combining Small measures into "All Other" category with mnimal manual intervention"
Comments
I have successfully used the "Top" filter to generate "Top N" lists automatically without having to tweak a threshold value as you have described.
Set a filter on the field in question, perhaps "Doctor Names." In the General tab of the Filter, select "Use All Values." In the "Top" tab of the filter choose "By Field" and the number of elements you wish to include, perhaps 20. You can use any appropriate measure and aggregation as a basis for the ranking, in your case it might be Field: Doctor Names and Aggregation: Count.
Note: One issue with this is that Tableau does not seem to perform the filters in the order that they are listed in the filter shelf. To ensure that the "Top" filter is applied after other filters, add the other filters to the context. If you don't do this you may get fewer than the specified number of results because it is first applying the Top N filter, then other filters, reducing the size of the result set to
But this doesn't group the rest of the 180 doctors into an "All Other" grouping. It simply excludes them.
I missed that part. I knew it seemed too easy.
I've attached an example that does a pretty good job of what you are asking for using a RAWSQL statement to calculate rank.
First, create a calculated field that, for each record, counts how many records have a value less than itself, then adds one to convert it to a numerical rank (assuming you want the highest value to be ranked #1).
Formula:
RAWSQL_INT('SELECT COUNT("MLS No") FROM "Master$" WHERE "Price">%1',[Price])+1Then, create an Ad Hoc Group in which every value of the above field less than 20 is in a group of one (itself), checking the "Include Other" box to create a dynamic category of elements where the rank is greater than 20. This is mildly tedious but it is a one-time task. (For grouping and color-coding purposes I've also created an additional Ad Hoc Group field in which rank values 1-20 are in a single group.)
The catch with this solution is that the RAWSQL statement syntax varies depending upon what data source you are using, and that it precludes the use of some combinations of aggregations, at least with the Tableau Extract data source used in this example, and my limited knowledge of SQL syntax.
Hope this helps.
Hi Aaron,
This would appear to be an excellent solution to a ranking issue I'm currently faced with. I'm using a Tableau extract based on an Excel spreadsheet. When I use your RAWSQL statement (amended with my field names) I get just a single count which is the number 1.
The RAWSQL statement does not provide a ranking for each record as it does in your example. I simply get the number 1 against the first record in the view. (Please see attached twbx.)
What is required is something like the following:
RANK IMC Amount Desc
1 123 6789 Build
2 345 6678 Car
3 456 3345 House
4 123 2345 Land
5 678 1234 Power
Any help you can provide would be most appreciated.
Many thanks
Michael
The issue here seems to be that you need the SQL statement to reference an alias of your data table.
In the sample I posted the table called "Master$" had an alias called "Master" so the SELECT statement could aggregate the rows in "Master$" with the COUNT() function, comparing field values to individual rows in "Master".
Problem is, I do not know how to create an alias outside of the statement itself; it already conveniently existed in the document I used for the example.
Seems to me this topic should move out of "Basics" and into the "Data Sources" or "Calculations" forum. I am sure there are some SQL heavy hitters who could weigh in here.
Many thanks, Aaron. for explaining the underlying logic used in the sample view. I'll now try and mimic this in my real-life model, i.e., create an alias table which the RAWSQL statement can reference.
It would be nice if Tableau were to include a RANK function in a future release.