If you are new to Tableau or you have not encountered the INDEX() function before you are probably familiar with your filters behaving oddly. The traditional filter use behaves like an AND statement, do the more filters you use and the more complex the view becomes the more difficult it becomes to ensure your filters show the data you want (and we aren’t even talking about measure filters or using the include or exclude LOD functions covered in my previous blogs here and here).
This blog will provide an easy solution and explanation to return your top 5, 10, 15, 20, etc. results.
The Conventional Method
To demonstrate this tip, consider the following view showing the number of recipes in a cookbook by category from the Meal DB API dataset. The view is currently filtered to show recipe categories under “British” cuisine that have spent at least 2 entries in the book.
If we wanted the Top 3 categories the traditional method would be to add a Category filter like below.
And we get this erroneous result.
As you can see we should see Beef, Seafood, Vegetarian, Lamb and Starter but we get Beef, Seafood, Vegetarian, Pork and Chicken? This happens because the filters are acting as AND statements, so all criteria have to be met, and Lamb and Starter are not in the top 5 category by recipes overall. They are only in the top 5 of British recipes.
The INDEX() Method
Using INDEX() we set up a calculated field that looks like this, replacing the “5” with the number of records that you want to keep.
INDEX() <= 5
What the INDEX function does is assign a row number to the data in the view, so if your view is sorted in descending order, the top 5 will be kept on the view. If your view is sorted in ascending order, the first 5 rows will remain, which is actually the lowest 5 count of recipes. As a Boolean calculation it is much more efficient also, either the row is <=5 or it’s not.
Now instead of the imposter-ish Pork and Chicken we have the lovely succulent Lamb and Starter as intended. Have fun filtering.
