In Business Intelligence, dates are an essential part of analysis, and they are an important part of Business Intelligence projects. Data warehouses, for example, have a Date dimension as a way of helping business users to describe their data by date. People's business questions often include a when element. Additionally, dashboards will often reference dates.
Comparison is fundamental to analysis. Time is a fundamental part of comparison. Dashboards will often display comparisons between periods of time, so time is an essential part of the dashboard display. It is easy to envisage the following business questions that involve time:
- What are my numbers compared to last year?
- How did my sales region perform this month as compared to the last month?
- When will my department reach its target?
This recipe explains how to use dates in order to analyze your data using the Dates functionality in Tableau.
In the last recipe, we imported the FactInternetSales table. Once the FactInternetSales data has been imported, we will also do the same for the DimDate table. To analyze our data, we will perform the following actions:
- Join data together
- Activate links in relationships
- Date analysis in Tableau
We will continue to use the same Tableau workbook that we set up in the first recipe. It was called Chapter 2.
- Let's create a new sheet by going to the tab at the foot of the worksheet with the worksheet name on it. Right-click to select Duplicate Sheet. Let's make the canvas clean again by removing all of the pills from the Columns, Rows, and Marks shelves.
- Let's use the new worksheet to proceed.
- Go to DimDate#csv under Dimensions, and look for Calendar Year.
- Make sure Calendar Year is set to the String data type by right-clicking on Calendar Year on the Data pane, then navigating to Change Data Type, and finally selecting String.
- In the Data pane, select Calendar Year and drag it into the Rows shelf.
- Go to the
FactInternetSalestable on the Measures pane. Drag the SalesAmount column from the Measures pane to the column next to the Calendar year on the canvas. You can see this in the following screenshot:
- Let's look at what happens if we use a different aggregation, Count Distinct. This returns a count of the distinct SalesAmount values. When we link the
OrderDateKeycolumn and select Count Distinct for the SalesAmount figure, Tableau returns the Count Distinct value for each calendar year. You can see an example of this in the following screenshot:
- In Tableau 8, if the aggregation is considered not valid, Tableau saves the user from themselves by graying out the screen until it is fixed. You have the facility to undo the last step. To do this, press Ctrl + Z.
While a formula might be technically valid, it may not make much business sense. Tableau allows business users to enrich their data through the addition of calculations that get stored as part of the workbook. This is useful for data analysis, since we can look and see how simple steps can quickly affect the data.
Why is this the case? Tableau issues separate queries to each data source and joins the two data sources together. Then, it conducts the aggregation on the joined data sources at the lowest level of detail in the view of the data from the Tableau interface. Earlier in the book, we joined all of our sources together in one place, which Tableau then reads. If we pull data in from different sources, we have to consider the granularity.
Unfortunately, if the level of detail in the underlying query is different from the level of detail in the view, then the calculation will not be correct. In this case, they would be said to have different levels of granularity.
The moral of the story is to keep the relevant dimensions in the Tableau view, that is, put their features in the Columns and Rows shelves or in the Marks shelf. By putting more elements in the view, these shelves will move the query towards serving up data that can be used for matching the data sources. Then, the user can try to incorporate the query by including as many dimensions as possible, which in turn will produce as much detail in the query as possible in order to facilitate matching between the tables.
Dashboards use a lot of calculations to summarize data. Research by specialists such as Ben Shneiderman shows that people tend to want to see the summary first, followed by zooming and filtering the data, and then finally see the details on demand. This is a very natural way of engaging with data. Shneiderman calls this the "Visual Information Seeking Mantra".
If you are interested in the psychology of how individuals interact with data, then Ben Shneiderman's paper The Eyes Have It (1996), which you can find at http://dl.acm.org/citation.cfm?id=834354, will be of help.