Chapter 3. Interacting with Data for Dashboards

In this chapter, we will cover the following topics:

• Fun with filters – grouping your data with clarity
• Hierarchies for revealing the dashboard message
• Classifying your data for dashboards
• Actions and interactions
• Drilling into the details
• Working with input controls

Introduction

A key aspect of dashboarding is that a dashboard should convey its message clearly and simply in order to help team members draw the right conclusions. Dashboards became more interesting to businesses when Kaplan and Norton introduced their Balanced Scorecard methodology in the 1990s. This introduced the dashboard as a way of measuring business performance with a particular focus on Key Performance Indicators (KPIs), which helped to measure the success and direction of the organization. With the Enron scandal in 2001, businesses realized that it was perfectly possible to drown in data and not really understand what is going on at the executive level. Therefore, the dashboard concept gained renewed interest, which continues to date.

Creating dashboards is both a top-down and bottom-up process. It is top-down because we need to be able to summarize and put all of the pieces together. It is also a bottom-up process because the dashboard is made up of its constituent parts. In this chapter, we will look at making the most of the constituent parts, so this chapter uses the bottom-up approach. Later on in this book, we will look at top-down processes while creating the dashboard.

In order to achieve the objective of conveying the message of the data effectively, users should be able to interact with data to get the information that they need. The subject of this chapter is to help you create dashboards that will facilitate team members to get the most out of their dashboards by setting up interactivity, navigation, and an awareness of the underlying data. According to the guidelines, such as Eight Golden Rules of Interface Design by Professor Ben Shneiderman, it is vital to allow users to interact with the data by offering the filtering, categorizing, and zooming in functionalities to access the details. Business users gain trust in the data by having a look at the details, which allows them to validate the truth of the data.

Interacting with dashboards is a vital way of allowing business users to understand the data better. It also allows components to tell stories individually as well as provide a coherent story of the data as a whole.

From the practical perspective, it allows us to make the most of the space. Instead of having lots of reports with different dimensions and filters, we can help the user move towards Self-Service Business Intelligence (SSBI). We do this by furnishing the user with the data that they require in a dashboard format while allowing them to focus on the dimensions and attributes that are most important to them.

Dashboards are different from reports in that users expect to be able to view the data and understand it at a glance. In other words, very little interaction is required as the necessary data should be presented; that said, it is expected that the data is highly integrated and that the various elements of the dashboard are highly coupled together.

This section will help you to see the different ways of facilitating interactions with the data on the dashboard while getting the message of the data across as quickly and effectively as possible. Currently, we are in a bottom-up part of dashboard creation, and not top-down. In Tableau, we create worksheets that then go into the dashboards. This is why we will initially focus on worksheets.

Fun with filters – grouping your data with clarity

Filters are a useful way of helping users focus on particular aspects of the data that they are most interested in. This helps them to investigate and compare data and perhaps look for outliers and exceptions in the data.

Filtering data is an essential part of a dashboard. Users like to interact with data in order to understand it better, and it is natural to filter data so that users can pinpoint the data that particularly interests them.

Tableau allows users to filter measures, calculations, and dimensions, which is extremely useful in a dashboard. For example, take the case where you need to see the sales figures that are less than the given amount over a period of time.

In this recipe, we will import some more data and look at taking some descriptive statistics. We will also look at filters. Users have a lot of flexibility when it comes to combining filters, which means that you can have a lot of creativity in your analyses.

Getting ready

For this recipe, we will need to have Tableau open and ready to create a new workbook. For the exercises in this chapter, we will import multiple tables into our new workbook as the basis for creating calculations.

Let's create a new folder to store the Tableau workbook. For example, in this book, we will use the folder Chapter Three under D:\Data\TableauCookbook\. Therefore, you will need to have the folder open where you will download the data files. In Chapter 1, A Short Dash to Dashboarding!, we specified D:\Data\TableauCookbook as an example. We will be importing the following files:

• DimProductCategory
• DimProductSubCategory
• DimProduct
• FactInternetSales
• DimDate

How to do it...

1. Open up Tableau and navigate to File | New. This will start a new workbook in Tableau.
2. Go to File and then click on Save As.
3. Save the file as Chapter Three.
4. We will connect to the data and import it into Tableau's data store. To do this, select Connect to Data, which you will see under Data.
5. Navigate to the location where you stored the CSV files. The following table shows the links between the tables:
   
6. Next, select Text File from the options of Connect to Data. You will see it in the section labeled In a file.
7. The first file we will import is called DimProductCategory, so select it and click on Open.
8. The Text File Connection dialog will appear. In Step 1, you will see the file path and the name of the selected DimProductCategory file.
9. In Step 2, select the option The first row has field names in it.
10. In Step 3, select the Multiple Tables option. You will see that the Text File Connection dialog will change, and you will see the name of the DimProductCategory file appear in the Table Alias part of Step 3.
11. We will be joining multiple tables, so click on the Add Table… button. The Add Table dialog box will appear.
12. In the Table tab, select DimProductSubCategory. You will see the fields for this table appear in a box under the Table Alias heading.
13. In the Join tab, you will see that the DimProductCategory table and the DimProductSubCategory table are joined together via ProductCategoryKey.
14. The value of Join Type should be set to Left.
15. In the following screenshot, you can see the join between the DimProductCategory and DimProductSubCategory tables:
    
16. Click on OK. This will take you back to the Text File Connection dialog box.
17. The next step is to join the DimProductSubCategory and DimProduct tables together. To do this, click on the Add Table… button again.
18. In the Table tab, click on DimProduct#csv.
19. In the Join tab, you will see that the DimProductSubCategory and DimProduct tables are joined together by ProductSubcategoryKey. The join type should be Left.
20. In the following screenshot, you can see the join between the DimProductSubCategory and DimProduct tables:
    
21. Click on OK. You will be taken back to the Text File Connection dialog box.
22. Let's now join the DimProduct and FactInternetSales tables together. In the Text File Connection dialog, click on the Add Table… button again, which will bring up the Add Table dialog box.
23. In the Table tab, look for the file named FactInternetSales#csv, which contains the data for the FactInternetSales table, and select it. You will see the fields for this table appear in a box under the Table Alias heading.
24. In the Join tab, you will see that the DimProductCategory table and the FactInternetSales table are joined together via ProductKey.
25. The join type should be set to Left. Click on OK, and you will be taken to the Text File Connection dialog box.
26. In the following screenshot, you can see the join between the DimProduct and FactInternetSales tables:
    
27. Finally, let's join the DimDate and FactInternetSales tables together. In the Text File Connection dialog box, click on the Add Table… button again, which will bring up the Add Table dialog box.
28. In the Table tab, look for the file named DimDate#csv, which contains the data for the DimDate table, and select it.
29. You will see the fields for this table appear in a box under the Table Alias heading in the Add Table dialog box.
30. In the Join tab, you will see that the DimDate and FactInternetSales tables are joined together via DateKey.
31. The join type should be set to Left. Click on the Add button in the Add Join Clause section, which you will see in the middle of the screen.
32. Click on OK, and you will be taken to the Text File Connection dialog box.
33. In the next screenshot, you can see the join between the DimProduct and FactInternetSales tables.
    
34. Click on OK. When prompted, select the Import all data option.
35. Once all of the tables have been imported, you will be taken to the Tableau canvas.
36. On the Data pane in the Tableau side bar, let's rename the data source to something more meaningful. Right-click on the data source under the Data pane. By default, Tableau will have given it a name that combines the first table and then the file type. Here, it will be called DimProductCategory #csv. Right-click on it and select Rename.
37. Enter CombinedProductsWithFacts so we know that this data source is a combination of facts and products.
38. We can look at putting in dimensions and metrics in order to make a start and be productive straightaway with Tableau.
39. Tableau places the DateKey field into Measures. The next step is to drag DateKey from Measures to Dimensions in order to be able to set up some of the relationships.
40. Next, we will visualize a table as the starting point. Take the FullDateAlternateKey field from the DimDate table and drag it onto the Columns shelf. Tableau will automatically recognize that this is a date, and it will aggregate the data according to the year level. Therefore, it will appear as Year(FullDateAlternateKey).
41. Next, take the EnglishProductCategory attribute from the DimProductCategory table and place it on the Rows shelf.
42. Drag SalesAmount to the canvas.
43. Then, we will add a few table calculations as an exercise to explore this concept more while also adding to our filters in this exercise.
44. On the Marks shelf, right-click on the Sum(Sales Amount) metric and select the option Add Table Calculation, as shown in the following screenshot:
    
45. When we right-click on the Sum(Sales Amount) metric, we get the Table Calculation window, as shown in the following screenshot. For our purposes, we want to choose Difference From as the value for Calculation Type.
    
46. We will calculate the difference along the table, so we will choose to calculate the difference along with the Table(Across) option.
47. In the Calculation Definition panel, we will choose the Previous option under the Display the value as a difference from: dropdown.
48. Once these options have been selected, we can customize the table calculation further by renaming the calculation to something meaningful. To do this, click on the Customize button, which can be found at the bottom-left corner of the Table Calculation box.
49. After we have clicked on the Customize button, we will get the Calculated Field dialog, which you can see in the next screenshot. The text button at the top is labeled Name: and we can insert a different name in this textbox.
50. Here, we will rename the table calculation to YoY Sales Difference. The formula itself works out the current sales amount and compares it to the previous sales amount. If a null value is found, for example, where there is no previous sales amount available because we are looking at the data for the first year, then a zero is returned; this is the job of the ZN expression. Once you have renamed the table calculation, click on OK.
    
51. You are then returned to the previous window, and you will see a description of the formula in the Description window. You will also see the formula in the Formula box. When you reach this point, click on OK, as shown in the following screenshot:
    
52. Drag Sum(Sales Amount) from the Measures part of the side bar over to the Marks shelf.
53. Drag Sum(Sales Amount) from the Measures part of the sidebar over to the canvas so that it appears in the table along with YoY Sales Difference.
54. Right-click on the Sum(Sales Amount) measure on the Marks shelf and choose the option Quick Table Calculation from the menu list. Then, select the Moving Average option. You can see this in the following screenshot:
    
55. This will create a new measure that shows the moving average. To rename the new calculated measure, right-click on the SUM(SalesAmount) measure in the Measure Values shelf and choose the Edit Table Calculation option.
56. In the Table Calculation [Moving Average of SalesAmount] dialog box, select the Customize… option.
57. In the Name: box, rename it to Moving Average and click on OK.
58. To summarize, we will now have two measures: one for Year on Year changes and another for the Moving Average Difference over time.
59. Our next step is to work out the difference between the two calculations that we have just made. In other words, what is the difference between the year-on-year change and the moving average for the sales amount?
60. Our first step in this process is to create a new calculated field that will work out the difference between the year-on-year change and the moving average. To do this, firstly we will need to go to the Analysis tab at the top menu item and select the Create Calculated Field option. You can see this in the menu in the following screenshot:
    
61. We will now get the Calculated Field editor box, and we need to subtract Moving Average from the Year on Year average. We can see this in the following illustration:
    
62. When we place all the three calculations on the table, it looks a little confusing with a lot of numbers, and it is hard to differentiate the difference between patterns and outliers contained in the data. The following is an example:
    
63. We can use our measures in order to filter the data, and it's very simple to do this. Drag the measure Difference between YoY Sales and Moving Average to the Filters shelf on the left-hand side. A Filter window will appear; then, an example appears, as shown in the following window:
    
64. In this example, we have chosen At Least as the filter option, so the Tableau worksheet will only show rows where the difference between YoY Sales and Moving Average is at least 1. This means that negative numbers will not be shown along with rows where there is no difference.
65. If we change the visualization of the chart to a heat map, the largest differences are clearly seen in the years 2006 and 2007.
    
66. If we click on the data points, we can obtain more details of specific values. Tooltips are an extremely useful way of providing additional details on demand without cluttering the real estate on the screen for people who do not need it.
    
67. To summarize, in this section, we have shown that we can use table calculations and measures in order to filter data to show the information that we would like to see on the dashboard. This helps to provide the "at a glance" purpose of a dashboard.

How it works...

Tableau matches are based on column names initially. It also makes a decision on whether a column is a dimension or a measure, based on the data type. So, in this example, the OrderDateKey field was inserted as a measure, and we needed to drag it to the Dimension area of the Data pane. Tableau does this by default because the DateKey field is an integer. If we leave it as a measure, it will not show up as a column available to define the join.

See also

• Designing the User Interface: Strategies for Effective Human-Computer Interaction, Ben Shneiderman, Cath Plaisant, Maxine Cohen, Steven Jacobs, Prentice Hall

Hierarchies for revealing the dashboard message

It can become difficult to manage data, particularly if you have many columns. It can become more difficult if they are similarly named too. As you'd expect, Tableau helps you to organize your data so that it is easier to navigate and keep track of everything.

From the user perspective, hierarchies improve navigation and use by allowing the users to navigate from a headline down to a detailed level. From the Tableau perspective, hierarchies are groups of columns that are arranged in increasing levels of granularity. Each deeper level of the hierarchy refers to more specific details of the data.

Some hierarchies are natural hierarchies, such as date. So, say Tableau works out that a column is a date and automatically adds in a hierarchy in this order: year, quarter, month, week, and date. You have seen this already, for example, when you dragged a date across to the Columns shelf, Tableau automatically turned the date into a year.

Some hierarchies are not always immediately visible. These hierarchies would need to be set up, and we will look at setting up a product hierarchy that straddles across different tables. This is a nice feature because it means that the hierarchy can reflect the users' understanding of the data and isn't determined only by the underlying data.

Getting ready

In this recipe, we will use the existing workbook that you created for this chapter.

We will use the same data. For this recipe, let's take a copy of the existing worksheet and call it Hierarchies. To do this, right-click on the Worksheet tab and select the Duplicate Sheet option. You can then rename the sheet to Hierarchies.

How to do it...

1. Navigate to the DimProductCategory dimension and right-click on the EnglishProductCategoryName attribute.
2. From the pop-up menu, select the Create Hierarchy feature. You can see its location in the following illustration:
   
3. When you select the option, you will get a textbox entitled Create Hierarchy, which will ask you to specify the name of the hierarchy.
4. We will call our hierarchy Product Category. Once you have entered this into the textbox, click on OK.
   
5. Your hierarchy will now be created, and it will appear at the bottom of the Dimensions list on the left-hand side of Tableau's interface.
6. Next, go to the DimProductSubcategory dimension and look for the EnglishProductSubCategoryName attribute. Drag it to the Product Category hierarchy under EnglishProductCategoryName, which is already part of the Product Category hierarchy.
7. Now we will add the EnglishProductName attribute, which we will find under the DimProduct dimension. Drag-and-drop it under the EnglishProductSubCategoryName attribute that is already under the Product Category hierarchy. The Product Category hierarchy should now look as follows:
   
8. The Product Category hierarchy will be easier to understand if we rename the attributes. To do this, right-click on each attribute and choose Rename. Change EnglishProductCategoryName to Product Category.
9. Rename EnglishProductSubcategoryName to Product Subcategory by right-clicking on the attribute and selecting Rename.
10. Rename EnglishProductName to Product.
11. Once you have done this, the hierarchy should look as follows:
    
12. You can now use your hierarchy to change the details that you wish to see in the data visualization. Now, we will use Product Category of our data visualization rather than Dimension.
13. Remove everything from the Rows shelf and drag the Product Category hierarchy to the Rows shelf. Then, click on the plus sign; it will open the hierarchy, and you will see data for the next level under Product Category, which are subcategories.
14. An example of the Tableau workbook is given in the following illustration. You can see that the biggest differences occurred in the Bikes product category, and they occurred in the years 2006 and 2007 for the Mountain Bikes and Road Bikes categories.
    
15. To summarize, we have used the Hierarchy feature in Tableau to vary the degree of analysis we see in the dashboard.

How it works…

Tableau saves the additional information as part of the Tableau workbook. When you share the workbook, the hierarchies will be preserved.

The Tableau workbook would need revisions if the hierarchy is changed, or if you add in new dimensions and they need to be maintained. Therefore, they may need some additional maintenance. However, they are very useful features and worth the little extra touch they offer in order to help the dashboard user.

There's more...

Dashboarding data usually involves providing "at a glance" information for team members to clearly see the issues in the data and to make actionable decisions. Often, we don't need to provide further information unless we are asked for it, and it is a very useful feature that will help us answer more detailed questions. It saves us space on the page and is a very useful dashboard feature.

Let's take the example of a business meeting where the CEO wants to know more about the biggest differences or "swings" in the sales amount by category, and then wants more details. The Tableau analyst can quickly place a hierarchy in order to answer more detailed questions if required, and this is done quite simply as described here. Hierarchies also allow us to encapsulate business rules into the dashboard. In this recipe, we used product hierarchies. We could also add in hierarchies for different calendars, for example, in order to reflect different reporting periods. This will allow the dashboard to be easily reused in order to reflect different reporting calendars, say, you want to show data according to a fiscal year or a calendar year. You could have two different hierarchies: one for fiscal and the other for the calendar year. The dashboard could contain the same measures but sliced by different calendars according to user requirements.

The hierarchies feature fits nicely with the Golden Mantra of Information Visualization, since it allows us to summarize the data and then drill down into it as the next step.

See also

• http://www.tableausoftware.com/about/blog/2013/4/lets-talk-about-sets-23043

Classifying your data for dashboards

Bins are a simple way of categorizing and bucketing values, depending on the measure value. So, for example, you could "bin" customers depending on their age group or the number of cars that they own. Bins are useful for dashboards because they offer a summary view of the data, which is essential for the "at a glance" function of dashboards.

Tableau can create bins automatically, or we can also set up bins manually using calculated fields. This recipe will show both versions in order to meet the business needs.

Getting ready

In this recipe, we will use the existing workbook that you created for this chapter.

We will use the same data. For this recipe, let's take a copy of the Hierarchies worksheet and by right-clicking on the Worksheet tab, select the Duplicate Sheet option. You can then rename the sheet to Bins.

How to do it...

1. Once you have your Bins worksheet in place, right-click on the SalesAmount measure and select the Create Bin option. You can see an example of this in the following screenshot:
   
2. We will change the value to 5. Once you've done this, press the Load button to reveal the Min, Max, and Diff values of the data, as shown in the following screenshot:
   
3. When you click on the OK button, you will see a bin appear under the Dimensions area. The following is an example of this:
   
4. Let's test out our bins! To do this, remove everything from the Rows shelf, leaving only the Product Category hierarchy. Remove any filters from the worksheet and all of the calculations in the Marks shelf.
5. Next, drag SalesAmount (bin) to the Marks area under the Detail and Tooltip buttons. Once again, take SalesAmount (bin) and drag it to the Color button on the Marks shelf. Now, we will change the size of the data points to reflect the size of the elements. To do this, drag SalesAmount (bin) to the Size button.
6. You can vary the overall size of the elements by right-clicking on the Size button and moving the slider horizontally so that you can get your preferred size.
7. To neaten the image, right-click on the Date column heading and select Hide Field Names for Columns from the list.
8. The Tableau worksheet should now look as follows:
   
9. This allows us to see some patterns in the data. We can also see more details if we click on the data points; you can see an illustration of the details in the data in the following screenshot:
   
10. However, we might find that the automated bins are not very clear to business users. We can see in the previous screenshot that the SalesAmount(bin) value is £2,440.00. This may not be meaningful to business users.

    How can we set the bins so that they are meaningful to business users, rather than being automated by Tableau? For example, what if the business team wants to know about the proportion of their sales that fell into well-defined buckets, sliced by years?

    Fortunately, we can emulate the same behavior as in bins by simply using a calculated field. We can create a very simple IF… THEN... ELSEIF formula that will place the sales amounts into buckets, depending on the value of the sales amount. These buckets are manually defined using a calculated field, and we will see how to do this now.

11. Before we begin, take a copy of the existing worksheet called Bins and rename it to Bins Set Manually.
12. To do this, right-click on the Sales Amount metric and choose the Create Calculated Field option.
13. In the calculated field, enter the following formula:

    If [SalesAmount] <= 1000 THEN "1000"
    ELSEIF [SalesAmount] <= 2000 THEN "2000"
    ELSEIF [SalesAmount] <= 3000 THEN "3000"
    ELSEIF [SalesAmount] <= 4000 THEN "4000"
    ELSEIF [SalesAmount] <= 5000 THEN "5000"
    ELSEIF [SalesAmount] <= 6000 THEN "6000"
    ELSE "7000"
    END

14. When this formula is entered into the Calculated Field window, it looks like what the following screenshot shows. Rename the calculated field to SalesAmount Buckets.
    
15. Now that we have our calculated field in place, we can use it in our Tableau worksheet to create a dashboard component.
16. On the Columns shelf, place the SalesAmount Buckets calculated field and the Year(Date) dimension attribute.
17. On the Rows shelf, place Sum(SalesAmount) from the Measures section.
18. Place the Product Category hierarchy on the Color button.
19. Drag SalesAmount Buckets from the Dimensions pane to the Size button on the Marks shelf.
20. Go to the Show Me panel and select the Circle View option. This will provide a dot plot feel to data visualization. You can resize the chart by hovering the mouse over the foot of the y axis where the £0.00 value is located.
21. Once you're done with this, drag-and-drop the activities. The Tableau worksheet will look as it appears in the following screenshot:
    

To summarize, we have created bins using Tableau's automatic bin feature. We have also looked at ways of manually creating bins using the Calculated Field feature.

How it works...

Bins are constructed using a default Bins feature in Tableau, and we can use Calculated Fields in order to make them more useful and complex. They are stored in the Tableau workbook, so you will be able to preserve your work if you send it to someone else.

In this recipe, we have also looked at dot plot visualization, which is a very simple way of representing data that does not use a lot of "ink". The data/ink ratio is useful to simplify a data visualization in order to get the message of the data across very clearly. Dot plots might be considered old fashioned, but they are very effective and are perhaps underused. We can see from the screenshot that the 3000 bucket contained the highest number of sales amount. We can also see that this figure peaks in the year 2007 and then falls in 2008. This is a dashboard element that could be used as a start for further analysis. For example, business users will want to know the reason for the fall in sales for the highest occurring "bin".

See also

• Visual Display of Quantitative Information, Edward Tufte, Graphics Press USA

Actions and interactions

We can make the dashboard more effective by highlighting certain aspects of data visualization. Basically, when the user hovers over the data point, it will highlight the column and row where the data point is found. Highlighting the data means that other irrelevant data points are grayed out, thereby emphasizing the relevant data points. This is a useful dashboarding tool because the relevant features are made more prominent, thereby enhancing the speed with which the data is understood.

We can create highlights using the Actions feature in Tableau. To create a highlight action, use the following options:

• For workbooks, we can find an Actions option under the Worksheet menu item.
• When we move towards creating a full dashboard, we can find dashboard actions under the Dashboard menu item. For now, we are looking at creating components that will go onto a dashboard, so we will stick with the worksheet feature for now.

Getting ready

In this recipe, we will use the existing workbook that you created for this chapter.

We will use the same data as before. For this recipe, we will take a copy of the Bins Set Manually worksheet and select the Duplicate Sheet option. You can then rename the sheet to Actions.

How to do it...

1. Once you have your worksheet in place, you will need to locate the correct Actions item. To do this, go to the Worksheet menu and look for Actions. You can see an example of this in the following screenshot:
   
2. This will bring up the Actions dialog box. The following is an example:
   
3. In the Actions dialog box, select the Add Action button; this will bring up some options. We will choose the Highlight option.
4. Once we have selected the Highlight option, you will see the Edit Highlight Action dialog box appear, which you can see in the next screenshot.
5. We will call this Bin Highlight Action, and it will be based on the Actions worksheet.
6. We will then choose the Hover option.
7. For the Target Highlighting option, select all of the fields. The dialog box will then appear as shown in the following screenshot:
   
8. Now, click on OK and then go back to the Tableau worksheet. We will change the Tableau worksheet so that we can see the result of the action.
9. On the Columns shelf, place the Product Category hierarchy, the SalesAmount Buckets, and the Year(Date) dimension attribute.
10. On the Rows shelf, select SUM(SalesAmount). We will place Year(Date) on the Color button.
11. Finally, select Discrete (Lines) from the Show Me panel in order to show the patterns over the years for each bucket type. You can see an example of this in the following screenshot:
    
12. If you hover the mouse over one of the bucket names, you will see that the relevant data points are highlighted. In the following example, when we hover the mouse over the 1000 bucket, we can see that it lights up the data points for that bucket; plus, the relevant years are highlighted. It's clear that other data points are grayed out.
    

How it works...

To summarize, we can use Actions to highlight data, and this functionality assists with the comparison process. Business users do not have to type in any information to achieve this result; a simple mouse hover will give them the patterns that they are looking for.

See also

• Show Me the Numbers: Designing Tables and Graphs to Enlighten, Stephen Few, Analytics Press

Drilling into the details

Filters are a useful way to help users focus on particular aspects of the data that they are most interested in. This helps them to investigate and compare data and perhaps look for outliers and exceptions in the data.

Filtering data is an essential part of a dashboard. Users like to interact with data in order to understand it better, and it is natural to filter data so that users can pinpoint the data that particularly interests them.

Tableau allows users to filter data based on measures, calculations, and dimensions, which is extremely useful in a dashboard. For example, take the case where you need to see sales that are less than a given amount over a period of time. Users have a lot of flexibility when it comes to combining filters, which means that you can have a lot of creativity in your analyses.

In this recipe, we will look at combining the high level with the detail view. In dashboards, we tend to stick to the summary data only. However, very occasionally, business users may ask for details as well, and this recipe caters to this particular scenario.

We create a dashboard, make the dashboard appear more appealing to business users, and help to make it read better. Further, more we will explore some of the options for delivering detailed data to the business users.

Let's make the current dashboard look better!

Getting ready

For the exercises in this chapter, we will continue to use the Chapter Three workbook. In this recipe, we will duplicate the Bins Set Manually worksheet and rename the duplicated sheet to Bins Set Manually Table.

How to do it...

1. Drag SalesAmount Buckets to the Columns shelf.
2. Drag Date to the Columns shelf to the right of SalesAmount Buckets.
3. Drag Product Category to the Rows shelf. The visualization should appear as follows:
   
4. We will swap the rows and columns so that the table does not use up as much space on the page. To do this, click on the Swap button that is located just under the Format menu item. The Tableau workbook will now appear as follows:
   
5. Now we need to show the chart and the table next to each another. To do this, we create a dashboard, which is how Tableau combines different charts and tables together.
6. We create a dashboard by navigating to the Dashboard menu item and selecting the New Dashboard option. You can see an example of this in the following screenshot:
   
7. A new dashboard will appear, and the tab will simply read Dashboard 1. To rename it, right-click and choose the Rename option. Here, we will call the dashboard, Bucket Analysis.
8. Currently, our dashboard is empty, so let's populate it with some tables. On the left-hand side, you will see a section named Dashboard, which contains the names of other worksheets in the Tableau workbook.
9. In our example, let's drag the workbook named Bins Set Manually to the dashboard area on the right-hand side. Then, drag the workbook named Bins Set Manually Table so that it sits under the Bins Set Manually chart. Your dashboard should now look as follows:
   
10. You will notice in the screenshot that we could make the columns align for each chart. So let's swap the bottom chart back to its original layout. Firstly, you will need to resize the bottom table so that it has the same width as the top chart.
11. Then, to improve clarity, you will need to resize the columns in the bottom table so that the column lines match the lines in the top table. You may find that resizing has affected the readability of some of the numbers in Bins Set Manually Table. Tableau gives us the ability to change the number format plus the appearance of the data in the table. We can make changes in order to make the data more readable.
12. We can easily change the format of the data so that it is much more readable, so let's do that first. To change the format properly, go back to the Bins Set Manually Table worksheet.
13. In the first instance, let's change the format of the data in the table by going to the Format menu item. You can find it easily by going to the Marks shelf, then to the Sum(SalesAmount) lozenge and clicking on the downward-pointing arrow on the right-hand side. You can see this in the following screenshot:
    
14. When you select the Format option, the left-hand side of the Tableau workbook changes to a specific format-based panel. Normally, the left-hand side panel is dedicated to Data. However, it changes flexibly in response to the user needs. You can see the Format panel in the following screenshot:
    
15. As the first step, we can remove unnecessary data by changing the number format to remove the pennies. This costs us two extra characters in space, which may not seem very much per cell. However, across the 13 columns, this soon adds up to 26 unnecessary characters. To remove the pence, go to the Numbers option in the Format panel and select the Currency (Custom) option.

    Make sure that the decimal places are set to zero; this will remove the pennies. This gives us more free space for more important data while retaining the width of the table to match the top chart.

16. You can also reduce the font size by selecting the Font option under the Default heading, as shown in the following screenshot:
    
17. The worksheet now appears as you can see in the following screenshot:
    
18. To change the sorting of the table so that the rows are sorted by the value of the data and not the name of the product category, we will need to go back to the Bins Set Manually Table worksheet.
19. Go to the Rows shelf, and you will find the Product Hierarchy dimension attribute. Right-click on it, and you will get a pop-up menu. Click on Sort, as shown in the following screenshot:
    
20. Once you have clicked on Sort, you will get a dialog box where you specify what you'd like to sort by. The following is an example of the dialog box:
    
21. If we sort the data in descending order, by value, then it will be clearer for the business users to understand it quickly. As shown in the previous screenshot, you will find a series of options for sorting the data. Here, we have chosen Descending as the value of Sort Order, and we have sorted by the SalesAmount field.
22. Now that the data is sorted, we can go back to our dashboard, and we see that the table corresponds much better with the chart. To make the dashboard flow better, we can remove some unnecessary items, thereby adhering to our earlier discussion on chartjunk as a distraction in dashboards. For example, if we right-click on the title of the Bins Set Manually table, we can hide the title so it does not form an interruption between the chart and the table. The option is shown in the following screenshot:
    
23. Now that we have removed the titles, the image is simpler. Further, more the chart and table are consistent with one another. We can also add in text to make the dashboard clearer.
24. We can see this from the following final screenshot:
    

How it works...

Tableau helps you to marry the summary and the detailed data by placing them together on the same page. When you click on a value in the legend, it highlights the appropriate values on the page. We have set up the configuration visually using Tableau so that we don't have to handcraft all of the programming language that occurs behind the scenes. This is stored in the .twb file, and we don't need to know any programming language to create dashboards.

Brushing the data gives us the opportunity to highlight and filter relevant data, which helps the business user to see emerging patterns in the data.

To summarize, we have used a combination of charts and details to create a dashboard. Normally, in dashboards, we focus on the summary of the most important information to help people make decisions. Occasionally, however, users will ask to be provided with the details and this technique allows people to see the pattern of the data alongside the actual detail. Some of you will notice, however, that there is something wrong with the ordering of the data in the table. The rows are ordered by the Product Category hierarchy, and these are ordered in alphabetical order. However, this does not match the data that appears in the chart above it. For example, if you take the 1000 bucket, you will see that the data points are accurately representing the value. So, the Bikes sales are represented by the values that appear at the highest point of the y axis. However, in the table below, the Bikes sales appear in the second row with the Accessories value above them.

Working with input controls

Removing some of the data can actually reveal more of the message of the data by narrowing the focus. Using filter controls in worksheets and dashboards is a way to pinpoint the data that you would like to show. Filters are very easy to set up, and their "clickiness" can help maintain "stickiness" in interacting with the dashboard itself.

In this recipe, we will create dashboards that provide a summary while adding filters to include an interactive aspect to the dashboard, thereby engaging users further in the data. We will filter by measure and then show how this filter can be used in a dashboard.

Getting ready

For the exercises in this chapter, we will continue to use the Chapter Three workbook. In this recipe, we will focus on the Bins Set Manually worksheet and rename the dashboard from Dashboard 1 to Sales Dashboard.

How to do it...

1. Take the SalesAmount metric from the Measures pane and put it in the Filters panel.
2. When you release the measure into the panel, you will see the following Filter Field dialog box pop up:
   
3. We will filter on the Sum value, so select Sum and click on Next.
4. Then, you will get the Filter dialog box, and an example of this is as follows:
   
5. In the worksheet, you can get a slider filter by simply clicking on the OK button. If you want the filter to appear as a separate slider, then right-click on the SUM(SalesAmount) filter and select the Show Quick Filter option. You can see this option in the following screenshot:
   
6. You can now see that the slider is located on the right-hand side of the dashboard. The following screenshot shows a close-up of the slider:
   
7. The numbers look a little odd, don't they? Let's make it look better. If you select the option to edit the filter, you can change the values of MIN to 0 and MAX to £7,000,000. Now it looks cleaner:
   
8. We have to decide whether or not we should include the NULL values. The option to show nulls is labeled Show Null Controls, and you can find it the pop-up menu, as the following screenshot shows:
   
9. The slider now has a drop-down list, which you can see in the following example:
   
10. Now, if you go to the Dashboard worksheet, you cannot see the SalesAmount filter. How do we make it appear? If you click on the small downward arrow at the end of the dashboard representation of the table, you will get a pop up.
11. Navigate to Quick Filters and select Sum of SalesAmount; you'll see the filter appear in the dashboard, as the following screenshot shows:
    
12. You can see the resulting dashboard in the next screenshot. Note that you can use the legend to filter along with the slider bar and the data points themselves. Users have many ways of interacting with the data, thereby increasing adoption.
    

How it works...

Filters are one of the most important features that automatically come with Tableau. As before, we set up everything visually, and the programming code is automatically generated behind the scenes. This means that it is easy to create Tableau dashboards as well as use them.

There's more...

Using filters is a key part of dashboards, since it allows people to understand the data better so that they can make decisions based on the data. Filters help business users by allowing them to interact with the data. Further "brushing" the data is a technique whereby we highlight the selected data points in order to see more details and gray out the irrelevant items.

Humans are thought to be able to hold only a limited amount of information in their heads at any one time; the "magic number" is thought to be seven, but it can often depend on the researcher. Highlighting selected data points helps us by focusing our attention on these data points and filtering the irrelevant material.