Show Table of Contents
For each mark in the view, a Rank table calculation computes a ranking for each value in a partition.
Suppose you are starting with the following text view, which shows sales totals broken out by year (from left to right) and by quarter and month (from top to bottom):
Instead of absolute sales values, you want to see how each month within a given year ranks in total sales, from most sales (ranked 1) to least sales (ranked 12).
Connect to the Sample - Superstore data source.
Click and drag the Order Date field in the Data pane and drag it to the Columns shelf.
The default date level is YEAR(Order Date).
Click and drag Order Date again and drop it this time on the Rows shelf.
Click on the right side of the field to open the context menu. Then choose Quarter.
You will see two options named Quarter. Be sure to choose the first one.
The field should now read QUARTER(Order Date).
Note: If you are creating the view on the web, the menu looks a bit different.
Click and drag Order Date a third time and drop it on the Rows shelf to the right of QUARTER(Order Date).
Click on the right side of the field to open the context menu and this time choose Month (again, choose the first of two options named Month). The field should now read MONTH(Order Date).
Drag Sales from the Data pane and drop it on Text on the Marks card.
You now have the basic view, showing Sales by Order Date over a four-year period, by month, quarter, and year.
Follow these steps to add a Rank table calculation to the basic view.
Click the SUM(Sales) field on the Marks card and then select Add table calculation.
In the Table Calculation dialog box, choose Rank as the Calculation Type.
Choose Table (Down) from the Compute Using list.
The highlighting in the view shows how this Compute Using value sets the scope of the calculation in the view:
Comparing the values in the original text view with the values in this view shows that the result is correct. The values run from 1 to 12, and the highest sales total in 2011, for September (81,777), is ranked 1 in the updated view, while the second highest value, for November (78,629) is ranked 2 in the updated view.
Click the X in the upper-right corner of the Table Calculations dialog box to close it.
Ascending order ranks values from least to most. Descending order ranks values from most to least. For Rank table calculation, the default value is Descending.
One issue with Rank calculations is that there may be more than one mark with the same value. What would happen, for example, if Tables in the Central region and Appliances in the South region both had sales of exactly $36,729? Tableau lets you specify how to handle such cases by including an additional field in the Table Calculation dialog box when you set Calculation Type to Rank.
The choices are listed below. The number sequence at the beginning of each option show how each option would rank a hypothetical set of four values where two of the values are identical:
|Competition (1, 2, 2, 4)||Identical values are assigned an identical rank. The highest value is ranked 1 and then the next two, identical values, are both are ranked 2. The next value is then ranked 4.|
|Modified Competition (1, 3, 3, 4)||Identical values are assigned an identical rank. The highest value is ranked 1 and then the next two, identical values, are both are ranked 3. The next value is then ranked 4.|
|Dense (1, 2, 2, 3)||Duplicate values are all given the same rank, which is the next number in the ranking sequence. The next value after the duplicate values is computed as though the duplicate values were a single value.|
|Unique (1, 2, 3, 4)||Duplicate values are given unique rankings, according to the direction in which the ranking is being computed.|