In this recipe, we will create a bar chart that can display the top N or bottom N countries based on CO2 emission, a world development indicator (WDI) tracked by the World Bank.
To follow this recipe, open B05527_03 – STARTER.twbx. Use the worksheet called Top N Bottom N, and connect to the CO2 (Worldbank) data source.
The following are the steps to create a bar chart with a top/bottom N filter:
- From Dimensions, drag Year to the Rows shelf.
- From Dimensions, drag Country Name to the Rows shelf, to the right of the Year pill.
- From Measures, drag CO2 Emission to the Columns shelf.
- Right-click the arrow beside the Dimensions section in the side bar, and select Create Parameter.
- Create a string parameter called Top or Bottom? with the following settings:
- Show the parameter control for Top or Bottom?. You can do this by right-clicking on the parameter, and selecting Show Parameter Control.
- Create a calculated field called
Country CO2 Rank, with the following formula:
- Drag the new calculated field Country CO2 Rank to the Rows shelf. By default, since this is a continuous field, it can only be placed to the right of Country Name and will produce an axis.
- Right-click on the Country CO2 Rank in the Rows shelf and select Discrete. This will change the pill color from green to blue.
- Move the discrete Country CO2 Rank pill and place it between Year and Country Name. Your Rows and Columns shelves should now look like the following:
- Right-click on the Country CO2 Rank pill, and change the Compute Using to Pane (down).
- Control drag the blue Country CO2 Rank in the Rows shelf to the Filters shelf to copy this pill.
- Click on OK to accept the defaults in the Filters window that appears. This leaves all the countries selected by default.
- Right-click on the Country CO2 Rank pill in the Filters shelf and select Continuous.
- Change the range in the window that appears to show
1to5.
- After you click OK, notice that the pill color of the Country CO2 Rank pill in the Filters card changes the pill from blue to green.
- Right-click on the Country CO2 Rank filter and select Show Filter.
- Right-click on the Country CO2 Rank filter, and select Edit Title. Change the title to Rank.
- Control drag the Year from the Rows shelf to the Filters shelf to create a copy of it. Accept all the defaults in the filter window that comes up.
- Right-click on the Year pill in the Filters shelf and select Continuous.
- Change the range in the window that appears to show
2008to2011. - Right-click on the Year filter and select Show Filter.
- Edit the CO2 Emission axis. Change the title to CO2 Emission - metric tons/capita.
- Test the Top or Bottom? parameter. Check that when you select Top, the graph shows the highest emitting countries. When you select Bottom, the graph should show the least emitting countries.
In this recipe, we allow the end users to select whether they want to display the top N countries (that is, countries with the most CO2 emissions) or bottom N countries (that is, countries with the least CO2 emissions). To do this, we need to use a parameter that determines if they went to the top or bottom.
To ascertain which countries belong to top or bottom, we can use the table calculation function Rank. In the Country CO2 Rank calculated field below, we determine whether the rank sorting is in ascending or descending fashion. If Top is selected, we want to sort descending (that is, the most first). If it is Bottom, SUM([CO2 Emission]) should be sorted in ascending fashion (that is, least first).
A key step to making sure the ranking works is that this Country CO2 Rank calculated field with the rank calculation needs to be placed as a discrete field between Year and Country Name. This will allow us to limit the scope per year, therefore allowing us to reset the ranking for each year. The field also needs to be discrete, because we cannot place a green (or continuous) pill between two blue (discrete) pills.
We have also re-used the Country CO2 Rank pill in the Rows shelf. We copied it to our Filter shelf so we can easily change which years are to be shown on the chart. By default, when we copy it, the pill is discrete and therefore shows all the values as checkboxes.
Leaving the Year filter as Discrete is not very user friendly. It will become quite tedious, and annoying, to have to check or uncheck each box every time the year range needs to change. For this reason, we change this copied field to Continuous.
You will most likely find yourself copying pills with table calculations around, and changing some of them to discrete and perhaps changing some of them back to continuous. Don't worry! This is a pretty common trick. We change the fields as we need them. Just remember that as soon as you copy, that pill becomes another pill that is no longer connected to the original pill. If you need to make changes, you need to remember you may need to change them in the other copies.
Please refer to the recipe in Appendix A, Calculated Fields Primer.