In the first recipe, we specified communication as one of the key features of a dashboard. We need to be able to share the right information with the right audience, at the right time, to the right people, and in the right format.
Sometimes, data needs to be translated so that it matches the business rules and the business understanding of the organization. Tableau offers a number of different ways that help you translate data into something that the business decision makers will understand.
Grouping is one way of making data meaningful to the business. In this recipe, we will look at grouping some dimension members into a single member. Rolling up some of the members in one dimension is a good way of summarizing data for dashboards.
In this recipe, we will look at grouping dimension members; then, we will look at more complex grouping of calculations. The business question is an investigation into the characteristics of customers, for example, those who have children, and those who do not. We will group the NumberChildrenAtHome dimension members into the group of customers who have children and those who do not.
Then, we will look at a more advanced example of grouping the data by measure rather than dimension. To do this, we can create a calculation that will distinguish the values that are below the average sales amount and above the average. Results that are classified as above average are labelled Above or Equal to Average, and below average sales are labelled Below Average. We can then use this calculation to convey a visual message to the business user; for example, we could color the above average sales in one color and the below average sales in another in order to make the distinction easily identifiable.
- We will connect to an Excel data file called
DimCustomer.xlsand import it into Tableau's internal data store mechanism. To do this, navigate to Data | Connect to Data. - Then, select the link Text File and a file browser will appear. Navigate to the folder where you stored the downloaded Excel and CSV files.
- Navigate to the
DimCustomer.csvfile and select it. - In the Excel Workbook Connection dialog box, change the Step 4 name so that it reads
Connection_DimDate. - You can now see the new data source connection on the Data pane.
- Click on Ctrl + M to get a new worksheet in Tableau. Alternatively, go to the Worksheet menu item and select New Worksheet.
- Click on the Connection_DimDate source and drag Number of Records from the Measures pane to the Rows shelf.
- Drag the NumberChildrenAtHome dimension attribute to the Tableau canvas, which is to the left of the
NumberOfRecordscolumn. You can see an example in the next screenshot. - When we look at the NumberChildrenAtHome dimension, we see the following members and the number of customer records associated with each member:
- We will group the dimension members so it is easier to see which customers have children and which do not.
- Go to the dimension called NumberChildrenAtHome, right-click on it, and select the Create Group option as shown:
- This produces the Create Group dialog box, which you can see in the following screenshot:
- In the Field Name field, enter the name of the field.
- Multiselect the numbers 1 through to 5 by holding the Shift key and clicking to select more than one number at a time.
- Click on Group, rename the group to
CustomersWithChildren, and click on OK. - You can then see the new group on the left-hand side on the Dimensions pane. Drag your new grouping to the Rows shelf.
- Then, choose Number of Records from the Measures pane and put it into the Columns shelf. You can see that the table now only has two rows in it: one that consists of a zero and another that has CustomersWithChildren.
- In order to make things clear, click on the zero and select Edit Alias.
- Rename the zero to
Customers with No Children. - You can also rename the Group alias to
Customers With Childrenso that it matches the format used elsewhere. To do this, select the numbers 1 to 4 in the table and right-click on them. - In the pop-up menu, select Group.
- Right-click on the new group and select Edit Alias.
- In the textbox, enter
Customers With Childrenand click on OK. - It is now clear from the table that over 11,000 customers have no children, whereas just over 7,000 customers do have children. We can visualize this information in a better way, and we will do this for the rest of the exercise.
- To do this, change the visualization to a heat map using the Show Me panel.
- We can then change the colors by dragging Number of Records to the Color panel on the Marks shelf.
- In order to show the difference between the customers who do and do not have children, the custom diverging color palette has been selected here.
- Two diverging colors of orange and blue are selected. Orange is selected for Customers With No Children because there is a greater number of customers who do not have children; brighter and more intense colors are often used in order to denote higher values, as shown in the following screenshot:
- To do this, stay in the
Chapter 2workbook and continue to work in the existing worksheet. - Right-click on the SalesAmount measure.
- Select the measure Create Calculated Field that is illustrated in the following screenshot:
- When you select this option, you will get the following dialog box:
- In the Name field, enter the name of the calculated field:
SalesAboveOrBelowAverage. - In the Formula field, we will put in a formula that will calculate whether or not the sales amount is above or below the average amount. The formula is as follows:
IF ( SUM([SalesAmount]) - WINDOW_AVG(SUM([SalesAmount]), First(), Last() ) < 0 ) Then 'Below Average' Else 'Above or Equal To Average' END
Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
- Once you've placed the formula into the calculation editor and clicked on OK, you will be returned to the main Tableau interface. You will see your new calculation on the left-hand side in the Measures pane.
- Drag the SUM(SalesAmount) measure to the Columns shelf.
- Drag NumberCarsOwned from the Dimension pane to the Rows shelf.
- Drag your new calculation SalesAboveOrBelowAverage to the Color button on the Marks shelf.
- Your screen should now look like the following:
To summarize, we have created a calculation that is meaningful to a business user. It provides the color display of the measure, which helps the business user understand things more efficiently. To summarize, it is simple and effective to conduct a grouping of dimension members into a binary grouping. This is useful for dashboards in order to provide an "at a glance" metric visualization that shows the organization has more customers who do not have children than those who do.
Essentially, this formula uses the WINDOW_AVG function to work out the average of the values that are in the Tableau view of the data. Basically, this average works out the value of the data that is viewable in the Tableau canvas and does not include data that has been filtered.
It uses First() and Last() to work out the average of all the data from the first row right until the last row. The calculation takes the current SalesAmount value and compares it with the average SalesAmount value.
Tableau allows you to group data together by simply arranging fields of your data source on a Tableau worksheet.
When you group fields in a worksheet, Tableau queries the data using standard drivers and query languages (such as SQL and MDX). It then groups data together wherever necessary. Finally, it presents a visual analysis of the data.