The INDEX() function returns the index of the current row in the partition, without any sorting with regard to value. The first-row index starts at 1. For example, the table below shows quarterly sales. When INDEX() is computed within the Date partition, the index of each row is 1, 2, 3, 4…, etc. so let us go through an example in Tableau you so you can really see what this means.
Note: I use the INDEX() function in pretty much every worksheet I build.
Worksheet
We are going to be a worksheet that will allow us to see the INDEX() function in action.
- Got to https://public.tableau.com/en-us/s/resources
- Click on Sample Data
- Download the FIFA World Cup Match Results sample data set
Note: There are quite a few fun data sets there so do have a look around. You can also find some nice data sets on Google Dataset search: https://toolbox.google.com/datasetsearch
- Start Tableau Desktop / Public
- Open the world_cup_results.xlsx
- Drag the World Cup – Tableau Format onto the data pane
- Click on Sheet1
- Drag Team onto Rows
- Drag Date onto Columns
- Create a new Calculated Field called Index with the formula:
INDEX() - Drag Number of Records onto Text
- Make sure you are looking at a table and observe the results
In a basic sense, you can see the number of records for each team for each world cup year. This is pretty simple stuff, but now let us drag the Index Calculated Field onto Text and see what happens. Now the whole table should be full of numbers which indicates the position depending on how it is calculated.
- Right click on Index, go to Compute Using and see that Table (across) is selected
- Select Table (down) and observe the results
- Check out the other options and observe the difference:
- Table (across then down)
- Table (down then across)
- Cell
- Date
- Team
My common usages for the INDEX() function includes:
- Top N Filters – More often than not I use Index to filter for the top or bottom N records
- Sorting – Sometimes I use this function for custom sorting
- Densification and Drawing – You can see this in a lot of my drawing tutorials
You can read more about the INDEX() function here:
- Tableau Calculation Functions: https://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_tablecalculation.html
- 5 Tableau Table Calculations that you need to know: https://interworks.com/blog/tmccullough/2014/09/29/5-tableau-table-calculation-functions-you-need-know
- How to use INDEX() for easier top n Tableau filters: https://www.ryansleeper.com/how-to-use-index-for-easier-top-n-tableau-filters
- Using the Index function to show the last 3 months of data collected: https://www.thedataschool.co.uk/jeremy-kneebone/tableau-using-index-function-show-last-3-months-collected-data
