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.

 

viz view

 

If we wanted the Top 3 categories  the traditional method would be to add a Category filter like below.

 

filter

And we get this erroneous result.

 

filter results

 

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.

index filter

Now instead of the imposter-ish Pork and Chicken we have the lovely succulent Lamb and Starter as intended. Have fun filtering.

The Data School
Author: The Data School