In Tableau, table calculations are used to calculate the values and fields within a view. In other words, table calculations depend on the data present in the worksheet's view for their computations. In this blog, I will discuss two important table calculation functions first() and last(), and demonstrate a use case based on a scenario. We will be using the Sample Superstore dataset for the demonstration.
First()
In the view, the first() function returns the number of rows from the current row to the first row in the partition. In other words, it calculates the number of steps required for the current row to get to the first value in the view.
Last()
In the view, the last() function returns the number of rows from the current row to the last row in the partition. In other words, it calculates the number of steps required for the current row to get to the last value in the view.
For example, let's first create a table in Tableau showing Subcategories and their sales amount. Drag the 'Sub-category' field in rows and 'Sales' in the 'Text' in the marks card. Then sort the 'Sub-category' in descending order by 'Sales'.
Let's create a 'First' field using the first() function in a calculated field.
Then, let's create a 'Last' field using the last() function in a calculated field.
Now, in the data pane, double-click the 'First' and 'Last' fields simultaneously. A table will be created as shown in the screenshot below.
In the above screenshot, we can see the first() function has assigned zero (0) to the first record and the values in the other records show the steps required for the current value to reach the first record. Likewise, the last() function has assigned a zer0 value to the last record and the values in the other records show the steps required for the current value to reach the last record.
What may be the use case scenarios for the first() and last() functions?
There can be various use cases in which we can use first() and last() functions in our calculation. Let us see one of the scenarios.
What are the Top 5 and Bottom 5 subcategories for sales?
Step 1: Drag 'Sub-category' in rows and 'Sales' in columns. Sort the 'Sub-category' by sum of 'Sales' in descending order. Note that sorting is important as the table calculation functions apply as per the view.
Step 2: Create a calculated field for 'Top 5 and Bottom 5 Subcategories' as shown in the screenshot below.
Step 3: Drag the 'Top 5 Bottom 5 Sub-category' in the filter shelf. Select 'True' and Click 'OK'
Now, let's take a look at the screenshot below. The bar chart shows the highest 5 and lowest 5 performing sub-categories, determined by their sales figures in the same chart.
