Basically, the INDEX() function in Tableau returns the position of the current row in the partition, without any sorting with regard to value. With the first-row value starting at 1. Sometimes though, we would like our index to behave in a certain logic that differs from the default behaviour. Problems with indexing/ranking can be quite frustrating and I will try to cover a problem I encountered on a recent project.
Let us consider the following scenario using the legendary Superstore dataset. You can also check out the workbook on Tableau Public and follow along.
In our view, we can see State and City Dimensions and Sales and Profit as Measures. We added State to Filters, for the end-user to interact with the view.
Our end users would like to see an overall index for the table based on a certain measure (e.g. Sales). On top of that, they would like to choose a State in order to check its cities while preserving the original index.
Creating an Index
First, we will create a new calculated field with the original name of the Index using index(). We will convert it to discrete and add it to the Rows.
The view will adjust itself with a table calculation as our index and adding State to account for each row of data.
Index based on a Specific Measure
We can now proceed with our first task: Index our table based on Sales in descending order. We will edit the Index table calculation:
- Right Click Index and Edit table calculation.
- Choose Specific Dimensions and check State and City (if not already checked).
- Check Sort order and change to Custom Sort.
- Check Custom and further choose Sales and SUM.
- Lastly, check Descending order.
Happy Days!
Now that we have our index in a descending order based on Sales, We can proceed. The other thing we need to check is the State filtering, bearing in mind that we expect to preserve the original index. In plain English, whenever we choose a State (e.g. California), we want its cities to preserve their relational position (e.g. Los Angeles, San Francisco, and San Diego should be indexed 2, 4, and 8 respectively).
Problems with Our Index ???
Something is definitely wrong because our Index has been re-calculated and we have our cities indexed as 1,2,3. This is clearly NOT what we want!
So what is going on with our Index???
Index() is a Table Calculation and is calculated at the very end. To better understand the underlying matter we need to recall Tableau’s order of operations.
Order of Operations
While Index() is a Table Calculation, Our State filter is a Dimension filter, and as such, is evaluated before the index(). We would basically like Tableau to query all states and filter them only at the end. But in our current state of affairs, Tableau queries the datasource only for the selected states and then index().
A possible solution for the situation would be to transform our Dimension State Filter into a Table Calculation, but how do we really accomplish that?
Dimension as a Table Calculation
The Lookup function returns the value of the expression in a target row, specified as a relative offset from the current row. Lookup function is commonly used when wanting to address values offsetting from the current value. In our example we will actually address the current value.
We will create a new calculated field (i.e. State Filter Calc) with the following syntax:
LOOKUP(ATTR([State]), 0)Which basically takes the current row value with no offsets.
We will drag State Filter Calc and replace the State Dimension filter. Nothing really changed in the view, except the newly replaced Table calculation filter.
Now let’s test our table calculation State filter and choose California. This time we do expect to get the original city positions since the query fetches all states and filters them only at the end.
Summary
Although Indexing/Ranking can be sometimes quite frustrating, it also gives us the opportunity to sharpen our skillset but also better understand the way Tableau works. To my experience, in these kind of of situations we tend to learn the most.