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.
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:DataTableauCookbook. 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:DataTableauCookbook as an example. We will be importing the following files:
DimProductCategoryDimProductSubCategoryDimProductFactInternetSalesDimDate
- Open up Tableau and navigate to File | New. This will start a new workbook in Tableau.
- Go to File and then click on Save As.
- Save the file as
Chapter Three. - 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.
- Navigate to the location where you stored the CSV files. The following table shows the links between the tables:
Table name
Linked To
Join
DimProductCategoryDimProductSubCategoryProductCategoryKeyDimProductSubCategoryDimProductProductDimProductFactInternetSalesDimDate - Next, select Text File from the options of Connect to Data. You will see it in the section labeled In a file.
- The first file we will import is called
DimProductCategory, so select it and click on Open. - The Text File Connection dialog will appear. In Step 1, you will see the file path and the name of the selected
DimProductCategoryfile. - In Step 2, select the option The first row has field names in it.
- 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
DimProductCategoryfile appear in the Table Alias part of Step 3. - We will be joining multiple tables, so click on the Add Table… button. The Add Table dialog box will appear.
- In the Table tab, select
DimProductSubCategory. You will see the fields for this table appear in a box under the Table Alias heading. - In the Join tab, you will see that the
DimProductCategorytable and theDimProductSubCategorytable are joined together viaProductCategoryKey. - The value of Join Type should be set to Left.
- In the following screenshot, you can see the join between the
DimProductCategoryandDimProductSubCategorytables:
- Click on OK. This will take you back to the Text File Connection dialog box.
- The next step is to join the
DimProductSubCategoryandDimProducttables together. To do this, click on the Add Table… button again. - In the Table tab, click on
DimProduct#csv. - In the Join tab, you will see that the
DimProductSubCategoryandDimProducttables are joined together byProductSubcategoryKey. The join type should be Left. - In the following screenshot, you can see the join between the
DimProductSubCategoryandDimProducttables:
- Click on OK. You will be taken back to the Text File Connection dialog box.
- Let's now join the
DimProductandFactInternetSalestables together. In the Text File Connection dialog, click on the Add Table… button again, which will bring up the Add Table dialog box. - In the Table tab, look for the file named
FactInternetSales#csv, which contains the data for theFactInternetSalestable, and select it. You will see the fields for this table appear in a box under the Table Alias heading. - In the Join tab, you will see that the
DimProductCategorytable and theFactInternetSalestable are joined together viaProductKey. - The join type should be set to Left. Click on OK, and you will be taken to the Text File Connection dialog box.
- In the following screenshot, you can see the join between the
DimProductandFactInternetSalestables:
- Finally, let's join the
DimDateandFactInternetSalestables together. In the Text File Connection dialog box, click on the Add Table… button again, which will bring up the Add Table dialog box. - In the Table tab, look for the file named
DimDate#csv, which contains the data for theDimDatetable, and select it. - You will see the fields for this table appear in a box under the Table Alias heading in the Add Table dialog box.
- In the Join tab, you will see that the
DimDateandFactInternetSalestables are joined together viaDateKey. - 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.
- Click on OK, and you will be taken to the Text File Connection dialog box.
- In the next screenshot, you can see the join between the
DimProductandFactInternetSalestables.
- Click on OK. When prompted, select the Import all data option.
- Once all of the tables have been imported, you will be taken to the Tableau canvas.
- 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. - Enter
CombinedProductsWithFactsso we know that this data source is a combination of facts and products. - We can look at putting in dimensions and metrics in order to make a start and be productive straightaway with Tableau.
- 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.
- 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).
- Next, take the EnglishProductCategory attribute from the DimProductCategory table and place it on the Rows shelf.
- Drag SalesAmount to the canvas.
- 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.
- 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:
- 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.
- We will calculate the difference along the table, so we will choose to calculate the difference along with the Table(Across) option.
- In the Calculation Definition panel, we will choose the Previous option under the Display the value as a difference from: dropdown.
- 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.
- 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.
- 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.
- 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:
- Drag Sum(Sales Amount) from the Measures part of the side bar over to the Marks shelf.
- 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.
- 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:
- 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.
- In the Table Calculation [Moving Average of SalesAmount] dialog box, select the Customize… option.
- In the Name: box, rename it to
Moving Averageand click on OK. - To summarize, we will now have two measures: one for Year on Year changes and another for the Moving Average Difference over time.
- 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?
- 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:
- 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:
- 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:
- 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:
- 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.
- If we change the visualization of the chart to a heat map, the largest differences are clearly seen in the years 2006 and 2007.
- 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.
- 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.
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.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.