Parameters are dynamic "placeholders" that can help to control the dashboards' appearance by storing information to drive the flexibility of the dashboard.
Parameters make our visualizations more accurate by controlling 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 to drive the display and look at enhancing the accuracy of the presentation by controlling the inclusion of NULL values on the dashboard.
If NULL values are present in the data, the resulting representation may be misleading. On the other hand, NULL values may 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 data quality issues rather than being distracted by the shininess of the technology.
Ultimately, the effectiveness of 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, depending on their choice. This recipe is quite complex 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 progress 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 Sparkline.
- After making a copy of the
KPI by Qworksheet and renaming itKPI Sparkline, let's create our first parameter. This is very easy to do; simply go to the Measures box on the sidebar of the Tableau workbook. Right-click anywhere inside this box and you will get a pop-up menu. When it appears, simply click on Create Parameter..., as 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 users to choose whether they want to display
Nullvalues. - First, let's give our parameter a name so that its purpose is explained precisely. We will call it
Show Data Points. 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 these fields have been completed, the 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 Measures 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 measures. 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. - The calculated field is called Difference (Show or Hide). The calculated rule incorporates a rule that says that if the Show Data Points parameter is equal to
1, then show the Difference between Actual and Target metric. If Show Data Points is not equal to1, then the rule fails, and it does not show anything at all. You can see our calculated field in the following screenshot:
- 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.
- Click on AGG(Difference (Show or Hide)) on the Marks shelf, and drag Difference (Show or Hide) to the Color button. The lines will now appear in different shades of red.
- 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 define 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 is represented by a line for 2005 and 2006, which ends up presenting 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 illustrates data to answer 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—a color normally used to denote a warning or a loss. Since we are not so interested in data where the countries met their targets, we will use the color gray so that this data goes into the 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, we can get two different shades of red and one gray color.
You can see this setting in the following screenshot:
- Once you have configured the color for the Difference between Actual and Target metric, 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, select the same Red-Blue Diverging option, but 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 to edit 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 color 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. The following screenshot shows the parameter control at the top-right corner:
- Let's configure the data visualization so that the
NULLpoints are displayed at the default position, zero. To do this, click on the downward arrow on the AGG(Difference Between Actual and Target) pill and select the Format… option. - Go to the Pane option in the Format Data pane on the left-hand side of the screen. At the bottom of the pane, go to the Marks option and select Show at Default Value.
- Click on the downward arrow on the AGG(Difference(Show or Hide)) pill, and select the Format… option.
- Go to the Pane option in the Format Data pane on the left-hand side of the screen. At the bottom of the pane, go to the Marks option and select Show at Indicator.
- If we choose the Hide Data Points option, then we can see that the United States has engendered an unacceptable loss, but the other countries have not. However, this shows the
NULLvalues, which assumes that all the countries have commenced at the same starting point, as shown in the following screenshot:
- If we choose the Show Data Points option, we can see the data points that are not
Null.
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 creative with our data 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 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.