Parameters are dynamic "placeholders" that can help to control the dashboard appearance by storing information to help drive the flexibility of the dashboard.
We can use parameters to make our visualizations more accurate by using them to control which data is to be displayed and which is to be hidden in response to user input. In this recipe, we will look at using parameters in order to drive the display and enhancing the accuracy of the presentation by controlling the inclusion of NULL values in the dashboard.
If NULL values are present in the data, then this might cause a misleading representation of the data. On the other hand, NULL values might be useful because they could tell us about the quality of the data itself. Sometimes, the data visualization project turns into a data quality project. Often, when data is visualized, it is the first time that business users have seen their data. This means that they can sometimes get a nasty surprise! Data can be missing, incomplete, or perhaps plain wrong. Therefore, it is important that dashboard consumers understand about data quality issues rather than being distracted by the shininess of technology.
Ultimately, the effectiveness of the data visualization rests on the accuracy of the data. In other words, even if the dashboard is perfectly formed, it will be no good if it shows inaccurate data.
In this recipe, we will use a parameter to give the user the opportunity to reveal or hide some data, dependent on the user's choice. This recipe is quite involved, because it covers parameters, dual axes, calculated fields, and some aspects of data visualization. So let's get started!
For the exercises in this recipe, let's continue with our Chapter 5 workbook so that we can see the progression from the initial KPIs to the end of the chapter. So, let's take the KPI by Q worksheet and make a copy of it; we will rename it KPI Shapes.
- Once you've made a copy of the
KPI by Qworksheet and renamed itKPI Shapes, let's create our first parameter. This is very easy to do; simply go to the Measures box at the sidebar of the Tableau workbook. Right-click anywhere inside this box and you will get a pop-up menu. You can see an example of this pop-up menu next. When it appears, simply click on Create Parameter..., which is highlighted in the following screenshot.
- Once you have clicked on the Create Parameter option, you will get a dialog box named Create Parameter. This allows you to configure the parameter. We will set up a parameter that will allow the users to choose whether or not they want to display Null values.
- First, let's give our parameter a name so that its purpose is explained precisely. We will call it
Show Nulls. This parameter is very simple. It is set to1ifNULLvalues are to be shown and set to0if theNULLvalues are to be hidden. Since we are using integers as a setting, we should keep the Data Type setting as Integer. The Current Value option gives the parameter a value as a starting point. Once you have completed these fields, your Create Parameter dialog box should appear exactly as shown in the following screenshot:
- We need to set up a calculation to control the parameter. To do this, create a new calculated field by right-clicking in the Parameter box again and choosing the Create Calculated Field option. We will use the calculation to make a rule that will drive the parameter. Our rule will specify that if the parameter setting is to show the
NULLvalues, the parameter value is set to1. This will display the second copy of the difference between the Actual and Target measure. If it is set to0, only then will the line graph show on its own, which will show theNULLvalues as well as the actual data. - You can see our calculated field in the next screenshot. The calculated field is called Difference (Show or Hide). The calculated rule incorporates a rule that says that if the parameter Show Nulls is equal to
1, then show the Difference Between Actual and Target metric. If Show Nulls is not equal to1, then the rule fails, and it does not show anything at all. The following is an example of the calculated field:
- Once you have created the calculated field, click on OK, and this will take you back to the Tableau workbook. Then, drag the Difference between Actual and Target measure and Difference (Show or Hide) to the Rows shelf, making sure that SalesTerritoryCountry is also on the rows. Then, choose the Dual Line Axis option from the Show Me panel. Your Tableau worksheet will look similar to the following screenshot:
How can we clearly differentiate where the actual data points are? The problem with the dual axis, as it stands, is that it will start at 0 if there is no data. This is because the axis is aligned to the year and the country, and if there is no data, it will simply map the data as having a value of 0. This can be misleading, however. For example, the data for Germany shows that there is a line showing data for 2005 and 2006, which ends up at a value of £1 million for the year 2008. However, this is a bit misleading; in fact, there was no data for the years 2005 or 2006; there was only data for 2007 and 2008. It would be better if this was clearer to the user.
Let's make the story of the data clearer to the business user by setting the colors and the line chart. This KPI panel is illustrating the data to illustrate an answer to a business question: which countries failed to meet their targets and when? This means that we are interested in emphasizing the losses made. We can do this by coloring these data points in red, which is a color normally used to denote a warning or a loss. Since we are not so interested in data where the countries made their targets, we will use the color gray so that this data takes a "background". Let's do this first for the Difference between Actual and Target data by dragging this measure onto the Color button. This will give us the following Edit Colors dialog box. Although we choose the option for Red-Blue Diverging, if we select the Stepped Color option and set it to 3 steps, then, we can get two different shades of red and one gray color. You can see an example of this setting in the following screenshot:
- Once you have configured the color for the Difference between Actual and Target metric, let's move forward to set the color for the Difference (Show or Hide) metric. Drag the Difference (Show or Hide) metric onto the Color button and you will get the Edit Colors dialog box. This time, we will select the same Red-Blue Diverging option, but we will choose 2 steps in the Stepped Color box, as shown in the following screenshot:
- Now let's make sure that the Difference between Actual and Target metric is set to a line. We can see this because the mark for the Difference between Actual and Target metric has a small line next to it, denoting that it is set to a line chart, as shown in the following screenshot:
- Now, click on the AGG(Difference(Show or Hide)) metric under the buttons, and this will reveal the buttons for editing this metric. In the drop-down list, choose Circle for the AGG(Difference(Show or Hide)) metric. This will distinguish it from the Difference between Actual and Target metric, which is denoted by a line graph, as shown in the following screenshot:
- We can set a border around the circles so that they are defined. At the same time, we can make the color transparent so that we get a layered effect. To do this, click on the Color button and you will get a pop-up menu. Set the transparency to 50 percent and choose a light purple for the border. You can see the options in the following screenshot:
- The last thing we need to do is to show the parameter control, which will give the user the option of explicitly showing the data points that are not
NULLor leaving the chart as is. To show the parameter control, right-click on the Parameters section and select the Show Parameter Control option from the pop-up list.
- The data visualization has now been completed, so let's test it out to see how it looks. In the following screenshot, you can see the parameter control at the top-right corner:
- If you choose the Show Nulls option, then you will get the data points appearing. This allows you to see which points are actual data and which points are
NULL. - If we choose the Hide Nulls option, then we can see that the United States has engendered a loss which was unacceptable, but the other countries have not. However, this shows the
NULLvalues, which assumes that all of the countries have commenced at the same starting point, as shown in the following screenshot:
- To summarize, using parameters to drive the data visualization, we can make our dashboards interactive and more sensitive to data quality.
To sum up, in this recipe, we have looked at data quality, calculations, parameters, and data visualizations. These are all interesting topics in their own right, and the objective of this recipe was to show that we can put them together in interesting ways in order to produce a dashboard. Tableau allows us to be very creative with our data in order to satisfy user requirements.
How did we use parameters in Tableau? To set up this visualization, we set up a dual line axis which has two measures on it: one is Difference between Actual and Target and the other is a calculated field that has a rule in it, which shows or hides a copy of the Difference between Actual and Target measure. Yes, in other words, we show this measure twice on the dual axis, or show the measure only once, depending on the choice of the user. The difference is in the way in which we represent each copy of the measure. One copy of the measure is a line graph, which is always shown, and the second copy is a dot plot, which only shows the data that is present. The parameter shows, or hides, the second version of the measure in order to show which data points actually exist.