Industry reports have shown that guided analytics is becoming an increasingly important requirement for mobile business intelligence requirements (Dresner (2013)). What is guided analytics? Guided analytics is defined as cases where knowledge workers can use data models to follow pathways of investigation towards their own results.
Users follow a pathway down the data, starting at a high-level summary of all of the available data, down to the specifics that they are interested in. Users don't have to start with a specific business question, but they can be directed down a pathway towards data that might be interesting and produce results.
Tableau can be used to create dashboards that can be adaptable in response to users' data explorations while also providing them with a pathway that is intuitive and helpful towards the goal of producing results using the dashboard. The topics in this recipe will help you to create dashboards that use guided analysis.
In order to enrich the guided analysis, we will add some new target data, which we can use to compare with the actual data. The comparison between actual and target data is fundamental to dashboards. We will set up actions to highlight fields in one worksheet based on input from another worksheet. We will set up some menu actions, which will pop up some additional menu items in order to help users to flow naturally through the data.
In this topic, we will use the existing workbook Chapter Four.
To proceed, we are going to import some more data.
- Create a new worksheet and call it
KPI Summaryby going to Worksheet and then selecting New Worksheet. - We will import new data that will give us target metrics for the purposes of our visualization. To do this, go to Data and then select the option Connect to Data.
- For our purposes, we will select the file called
FactSalesTargetand open it. - Then, we will select the Import all data option to import all the data into the worksheet. When we have imported the additional data, the Data shelf will hold two connections—one for the original data and another for the target data. We need a way to tie the two connections together. To do this, go to the Data menu item and select the Edit Relationships… option from the list.
- The Relationships dialog box helps to associate the data sources with each other. The data has the country, region, and group information in common, along with the year. We will need to link the columns together. You may find that the country information, region information, and group information have already been associated with each other by Tableau, and now we simply need to add the year. We are aiming to have the Relationships dialog box look like the example in the following screenshot:
- To add the year, click on the Add… button. This will bring up the Add/Edit Field Mapping dialog box. Then, type
yearin the Primary data source field and the Secondary data source field dialog boxes. You will find the YEAR(FullDateAlternateKey) and the Year(DateKey) fields. - Click on both of these fields and click on OK, as shown in in the following screenshot:
- Once we have set up the relationships, we can now add the data to the Tableau canvas. Click on the DimDate#csv data source in the Data shelf of the sidebar.
- Drag SalesAmount from the Measures pane and onto the Column shelf.
- Click on the FactSalesTargets#csv data source in the Data shelf of the sidebar.
- Drag SalesAmountQuote from the Measures pane onto the Columns shelf.
- Then, add the SalesTerritoryCountry dimension to the Rows shelf.
- From the Show Me panel, select the table option first of all so that we can see the data.
- You will find that one of the SalesTerritoryCountry fields is marked NA. Right-click on NA and select Exclude.
- Your Tableau canvas should appear as shown in the following screenshot:
- Now that we have blended our data together, we can create a calculated field in order to show very quickly whether there is a difference between the actual amount and the target amount.
- To set up the calculated field, go to the Analysis menu item and select the Create Calculated Field… option from the list. This will give us the Calculated Field dialog box, which you can see in the next screenshot.
- We will call our Difference between Actual and Target calculation, and it is a very simple calculation to do. We will work out the difference between the actual and the target profit. The target profit is called
SalesAmountQuotein the dataset. - To do this, we subtract the target sales amount, called
SalesAmountQuotein the dataset, from the actual sales amount. - You can see an example in the following screenshot. When you have set up the calculation, click on OK.
- Now, we have set up our actual metric, our target metric, and the difference between the two. We can now proceed to do some interesting guided analytics in putting the data together.
- We will start by creating KPIs, which will serve as our summary data. From this vantage point, we can drill down into the detailed picture of the data.
- Take our Difference between Actual and Target calculation and drag it to the Color button.
- Click on the Color button and choose the option Edit Colors….
- In the Edit Colors dialog box, choose Red-Blue Diverging from the Palette drop-down list. We will use the red and blue diverging color palette.
- We will use stepped color as before. However, instead of using the default five steps, we will use only three. Enter
3in the stepped color Steps box and then click on OK. - In the Marks shelf, change the mark type from Automatic to Square.
- Drag the Difference between Actual and Target calculation onto the Label button. Let's add some labels so that the boxes appear more like KPI tiles.
- Change the color to white and then click on the Edit Label dialog box. You will see the change in the table.
- Click on the Label button and change the font so that we can use the Segoe UI font with size 12.
- Next, we will make the KPI look more like a tile than a table. To do this, drag the SalesTerritoryCountry dimension attribute onto the Label button, as shown in the following screenshot:
- Now, we will concatenate the SalesTerritoryCountry name and the Difference between Actual and Target calculation value together so that the label reads nicely. When you click on the blue edit label button, a dialog box appears, which you can see in the next screenshot.
- Next, we will format the text so that we can use the Segoe UI font with size 12.
- When you click on the downward-facing arrow on the Insert button, you can select the SalesTerritoryCountry name and the Difference between Actual and Target calculation in order to show the country and the associated difference.
- You will find that the number format for the Difference between Actual and Target calculation means that the whole number is shown. This isn't very clear. In order to stay with our principles of eliminating chartjunk, let's amend the format so that it simply shows the million figure. To do this, right-click on the Difference between Actual and Target calculation.
- Go to Default Properties and click on Number Format…. This will bring up the Default Number Format box.
- Choose Number (Custom), as shown in the following screenshot:
- Firstly, let's get rid of the chartjunk. Let's get rid of the pennies; when we are talking about millions of pounds or dollars, pennies do not matter so much. Let's put the units to Millions (M) and prefix the amount with a pound sign or a dollar sign—whatever you prefer! Once you have done this, click on OK, as shown in the following screenshot:
- Now, you will notice that the labels read much more nicely in our KPI tile.
- Remove the row headers from the canvas by right-clicking on the heading and selecting the Show Header option.
- Our KPI tiles should now look as shown in the following screenshot:
- Let's add some actions so that we can navigate around. Before we move forward, let's duplicate the existing
KPI Summarysheet and rename it toKPI by Year. - We can now add some actions so that we can navigate around.
- In the new
KPI by Yearworksheet, click on the DimDate dimension, look for the FullDateAlternateKey dimension attribute, and drag it across onto the Columns shelf.
- Let's add the row labels back to the left-hand side so that we can compare more easily. To do this, click on the SalesTerritoryCountry dimension member in the Rows shelf and select the Show Header option.
- Let's change the color settings so that we show five steps rather than three, as per the previous example. This will reveal more fine-grained variations in the data.
- Once you have completed these simple steps, your Tableau visualization will appear as shown in the following screenshot:
- Let's add the row labels back to the left-hand side so that we can compare more easily. To do this, click on the SalesTerritoryCountry dimension and select the Add Headers option.
- We can also format the borders of the cells so that they are neater. To do this, click on the SalesTerritoryCountry dimension and select the Format… option.
- Using this dialog box, you can neaten up the borders by making them all white in order to give a nice finish to the cells. You can see how to do this in the following screenshot:
- Let's add another worksheet which will provide more detail. Duplicate the
KPI by Yearworksheet and call itKPI by Q. This only requires a few simple changes in order to provide a more detailed chart. - To start, simply click on the + sign on the Year(FullDateAlternateKey) dimension so that we see the Quarter(FullDateAlternateKey) dimension exposed on the Columns shelf. Now, we will see more columns on the visualization.
- To summarize, we now have three sheets:
KPI Summary,KPI by Year, andKPI by Q. We will now set up actions in order to link them together. Our Tableau actions will respond to user input. For example, if we click on Canada on theKPI Summaryworksheet, then we can see that the Canada fields are highlighted on theKPI by Yearworksheet. This is a very simple action which helps to draw attention to particular aspects of the dashboard, thereby helping the user interaction flow. - In all, we will create five actions that will facilitate all of these activities. Let's start with the actions to highlight fields. To do this, let's first go to the
KPI Summaryworksheet and begin adding some new actions by going to the Worksheet option and choosing the Actions… option. - Let's add in some highlight actions in the Actions dialog box by clicking on the Add Action > button and choosing the Highlight… option. We will create three highlights, one for each of the worksheets:
KPI Summary,KPI by Year, andKPI by Q. - For the first filter, let's rename it to
Highlight Country from Summary to Year. We will choose to run the action on the Select action. Otherwise, we will have a lot of noisy actions generated if the user simply hovers over a data point. This might mean that the user loses his/her thread in navigating the data, and we do not want that to happen. - For the Source Sheets option, let's stay with
KPI Summary. For the Target Sheets option, let's chooseKPI by Year. In the Target Highlighting section, let's choose to highlight some fields, which will be done by selecting the Selected Fields option. Once you've done this, your Edit Highlight Action dialog box should appear as shown in the following screenshot:
- For the remainder of the actions, let's repeat them using the details given in the following table:
Name
Run on
Source sheet
Target sheet
Fields
Highlight Country from Summary to QSelect
KPI SummaryKPI by QSalesTerritoryCountryHighlight Country from Year to QSelect
KPI SummaryKPI by QSalesTerritoryCountry. YEAR(FullDateAlternateKey)Filter Country from Summary to YearMenu
Connection name, for example,
DateDate#csv+ (FactInternetSales)KPI by YearSalesTerritoryCountryFilter Country from Year to QuarterMenu
Connection name, for example,
DateDate#CSV+ (FactInternetSales)KPI by QuarterSalesTerritoryCountry - Once you have repeated the creation of the actions with these attributes, we can test out some action scenarios.
- Let's test out the highlight actions first. On the worksheet, let's click on United States on the
KPI Summaryworksheet. When we do this, we can go to theKPI by Yearworksheet, and you can see that United States is highlighted. - You can vary which countries you show in the canvas by clicking on the action lozenge in the Filters shelf. For example, you could choose the option Show In/Out of Set to decide which details you want to show, as shown in the following screenshot:
- In setting up these actions, you have configured additional menu options as well. So, for example, if you right-click on the United States row in the
KPI by Yearworkbook, you will be presented with a number of options for filtering, as shown in the following screenshot. These menu options were set up automatically for you by Tableau when you set up the menu actions.
- So, if you select the Filter Country from Year to Quarter option, then you are taken to the
KPI by Qworksheet, and you can see the relevant data for the United States. Note also that the years are highlighted; this is as a result of the addition of these fields in the Selected Fields option.
To summarize, we have set up a lot of different options for navigating around worksheets and dashboards using Tableau actions. You can see that Tableau is extremely flexible and adaptable for customization, which helps users to get results from their dashboard explorations.
In setting up these actions, you have configured additional menu options as well. So, for example, if you right-click on the United States row in the KPI by Year worksheet, you will be presented with a number of options for filtering. These menu options were set up automatically for you by Tableau when you set up the menu actions.
When we changed the color, we removed the blue color completely from the visualization, and only red and grey remained. Using a neutral color conveys the message that those data points are OK since they have been brushed out. This means that the strong red color, representing the high negative difference between actual and target totals for the United States here, is emphasized by default.
Depending on your version of Tableau, the Label button may be called the Text button. However, the rest of the items have stayed the same.