Our examples so far have focused on comparing measures that occur along a time continuum. Table calculations are useful for a comparison between different dimensions as well. Ranking by discrete numbers, as well as percentiles, is powerful. Many people want to know the top or bottom number of members in a dimension. In the next example, the final product shows the bottom 20 percent of the countries in each region according to their availability of improved water sources, and each country will be colored by its percentile of life expectancy.
Start off by creating a basic bar graph from the World Development Indicators data source that shows the maximum percentage of a country's population that has access to improved water sources. Drag Region and Country to the Rows shelf. Then, when we drag Improved Water Source (%) to the Columns shelf, we aggregate it as a maximum rather than a sum. Select MAX based on the assumption that unless there's a major natural disaster (such as floods and earthquakes, which were used as data points in the previous chapter) or war, it's unlikely that the access to clean water will decrease.
Then, sort the countries in the ascending order by clicking on the sort icon on the x axis.
The real ranking tasks begin here, with the label. The objective is to show the bottom 20 percent by region. So, we need to do several things along the way that help explain the ranking functionality. Perform the following steps:
- Drag Improved Water Source (%) to the Label shelf.
- Then, click on it and select Quick Table Calculation.
- The Quick Table Calculation that we selected is Rank. Then, drag the field to the Measures pane and rename it to Country Rank – Water.
- Check out the snippet of the visualization in the following screenshot; each country is ranked in the descending order by the percentage of citizens who have access to clean drinking water:
- Note that the countries are ranked as a part of the whole table. We would like to rank them by region. Therefore, click on the pill, which is present on the Label shelf, select Compute using, and choose Pane (down).
- To the lower-right of the visualization, there is an indicator that we have several null values. This is because some of the countries have not reported these data points. Click on the indicator to exclude the countries with null values.
- There are other functions, such as
INDEX(), that perform similar tasks, except thatINDEX()actually produces the row number in the partition and not the rank. If you change the sort order of the countries shown, the labels stay the same; wherever you see Somalia, it will be ranked 53 in Africa.
The next task is to show the bottom 20 percent of the countries. Showing the top 20 percent would not add much value since the measure cannot exceed 100 percent of the population. In order to do this, we created a new calculated field called Percentile Filter, and told Tableau that we want to know whether the percentile of each country is less than 2. This means that it's in the bottom 20 percent of all the countries in its partition. Then, define the partition. The default is that the entire table is the partition, but we want each pane to be the partition.
The filter shown in the following screenshot has three possible results, namely true, false, and null, which makes it a Boolean field that shows up with a T|F icon in the data window. Since we excluded the nulls, we will not get null values:
In order to make sure that the total ranking of the countries in each region and showing only the bottom 20 percent, perform the following steps:
- Click on Default Table Calculation.
- Select Country.
- Click on OK.
- Click on OK again.
- Drag the field to the Filters shelf.
- Select TRUE.
You can also allow users to see the percentage of their preference by creating a parameter with a data type of float and a rank from 0 to 1.00 that replaces the hard-coded .2 in the formula, as shown in previous screenshot.
There are several functions within Tableau Public for ranking, and except for RANK_PERCENTILE, the other functions vary only in the way they use unique or duplicate ranking values.