How to use Date Field in Tableau
How to use Date Field in Tableau
Tableau recognizes dates that are contained in your source data and allows you to change the level of detail displayed via an auto-generated hierarchy. It is also possible to rearrange date levels by changing the order of date pills on the row or column shelves.
Diagram of combination filter applied to a bar chart
Discrete and continuous time:
You’ve probably noticed by now that some pills are green and others are blue. Similarly, icons can be in blue or green colors. most beginners believe blue pills and icons denote dimensions while green pills are used to display measures. While this is frequently the case, the truth is more subtle. Blue pills/ icons denote “discrete” fields. Green pills/ icons denote “continuous” fields. Dates can be both discrete and continuous. Below Diagram shows Tableau’s default way of displaying time— as discrete time hierarchy.
You can see that time has been discretely segmented in the time series chart by year. Clicking on the plus sign in the quarter pill would cause the date hierarchy to expand to include months, and panes for each quarter would be exposed. Continuous dates don’t discretely bucket time but will cause a drill down to a lower level of detail. Below Diagram shows a similar time series chart with continuous time being used and the level of detail being month.
The green pill on the column shelf in Below Diagram indicates the level of detail being displayed. Notice that there are no panes in view. Time is continuously displayed as an unbroken line.
Discrete time series Diagram
Continuous time series Diagram
Tableau’s date hierarchy:
Time can be expanded to more granular levels simply by clicking on the plus sign within the date pill. Experiment with this and note that you can rearrange time buckets just by changing the order of the pills by repositioning them. It’s also possible to change the level of detail displayed by right-clicking on the date pill. This exposes the menu in below diagram.
Diagram of changing the date level of detail
The menu includes two different date sections that start with year. The first group provides discrete date parts. The second group provides continuous date values. Below Diagram was created by changing the date displayed in by altering the quarter pill to display month.
Diagram of time series displaying discrete year-month
In Below Diagram note the menu option appears twice the first time it appears is, within the discrete date section of the menu. The second time it is in the continuous date section. Explore the menu option in both the discrete and continuous time portions of the menu. The more menu options provide even more granular options for controlling how date and time are presented in your view.
Rearranging time with Tableau:
There are many different date and time combinations that can be displayed. Below Diagram rearranges time to display weekday first, then year. Each day is a discrete time bucket. You can also add a reference line by pane that displays the average sales value for each weekday across all four years. This is one of the ways you can leverage discrete time to provide additional information.
Diagram of Rearranging time and applying a reference line
If your data supports very granular views of data, tableau can display details down to the second. This might be particularly useful if you need to analyze click stream data on a website.
Creating customized date fields in Tableau:
Diagram of Creating a custom date
Tableau’s date hierarchy is always available. Even people consuming reports via tableau reader or server can expand time. When hovering your mouse pointer over an axis you will see a small plus or minus sign appearing. Clicking on those signs expands or contracts the date hierarchy displayed.
Designers with tableau desktop can alter tableau’s default date hierarchy by creating custom date fields and then building unique date hierarchies. Making custom date hierarchies requires three steps:
- Create a custom date.
- Create the date hierarchy.
- Use the custom date in your view.
To create a custom date, point at a date field on the dimension shelf and right-click. This will expose a dialog box that provides a means for defining a custom date or time element as you see in below diagram.
Create a custom year date by naming the field “year” and defining the date as a discreet year date part. You can also add another discrete date for month. By dragging the custom month on top of the custom year, you can add a new custom date hierarchy. Below diagram shows the resulting date hierarchy.
You can see the custom hierarchy in below diagram on the dimension shelf. The year and month custom dates are displayed in this time series chart. In this way you can change how tableau expands and contracts the dates used in your visualizations.
Tableau’s date facility encourages explorations of data over different time slices because it is very easy to use and also it doesn’t require any special skill to master.
Custom date hierarchy Diagram
Taming Data with Measure Names and Values:
Sometimes your data isn’t clean and it may not be structured in a way that supports the analysis you need to perform. You might also be looking at a data set for the first time and need to scan it quickly to get a lay of the land. Tableau’s measure names and measure values fields help you with all these tasks.
What Are Measure Names and Measure Values?
Measure names and measure values do not exist in your datasource. These fields are generated by Tableau. Measure names hold all of the dimension names in your data set. Measure values hold all of the measures in your data set.
When to Use Measure Names and Measure Values
How can measure names and measure values help you understand new data? One way is by providing a means for you to quickly view all of the dimensions contained in your data set. These fields also allow you to combine multiple measures within a single axis. Below Diagram displays all of the measures in Tableau’s Superstore data set.
The cross tab in Below Diagram was created by double-clicking on measure names on the dimension shelf, swapping the axis, and adding order date on the column shelf. This technique provides a very quick overview of the data. The status bar at the bottom left tells you that there are forty data points and four years of data. Notice that the measure values shelf on the right provides details on the aggregation used to display the information. You can change that by right-clicking on any of those pills and picking another option.
Another way to use measure names and measure values is to tame data that isn’t structured in a way that facilities the analysis you want. Below Diagram includes a sales projection in a spreadsheet.
Diagram of using measure names and measure values
Diagram of sales forecast in spreadsheet form
Diagram of sales forecast in database form
Each product has unit sales projected in columns for each month in the year. Tableau will interpret each column as a separate measure. In a database, this information would be stored in a structure that looks like Below Diagram.
General-purpose databases normally store data in a row-oriented format. Tableau can connect to both kinds of data sources. If your data is more column-oriented, like Below Diagram, measures names and measures values provide a means for creating views that wouldn’t be supported otherwise. Below Diagram shows a time series chart that was built using the spreadsheet data source.
Diagram of Time series using the spreadsheet data source
Tableau interprets each column in the spreadsheet as a separate measure. So each column would be treated as a different measure. Measure names and measure values allow you to place multiple measures on a single axis. Even though the data format in the spreadsheet doesn’t support that type of view, using measure names and measure values allows you to achieve the desired presentation quickly.
If your data source was formatted like Below Diagram (like a database would store the information), measure names and measure values would not be required to make a time series chart like you see in Below Diagram . The column and row structure you see in Below Diagram directly supports the creation of the view without having to use measure names and measure values because month is contained in a discrete field ; product name is a discrete field. Placing the Product name field on the color button within the marks card would express each product with its own color. Placing the month field on the column shelf would put each month in its own column. Using a database structure eliminates the need for the use of measure names and measure values. Unfortunately, you may have to deal with spreadsheet structure and build views. Measure names and measure values help you deal with untidy data structure.
Advanced uses for measure names and measure values:
Measures names and measure values facilitate more advanced chart types as well. Your spreadsheet should also include unit price and cost information in a separate prices tab that looks like Below Diagram.
Diagram of Spreadsheet tab with unit price and cost data
Using tableau’s spreadsheet re-shaper tool (see appendix b), you can change the spreadsheet format to a column-oriented style like Below Diagram then you can join that tab to the prices tab in Below Diagram via the product code key record. This will allow you to create calculated values extending sales, cost, and gross margin dollars. Then you can create a time series view of the three measures as you see in Below Diagram.
Tableau’s default time series Diagram
Tableau’s default presentation of this information uses a separate axis to display each measure. Using measure names and measure values, you can combine all three measures on a single axis and use color to differentiate the values. Below Diagram shows the combined axis time series chart.
Diagram of Combining multiple measures on one axis
The combined axis chart in Below Diagram can be created a few different ways. Using the view in Below Diagram as a starting point, the most intuitive way to combine the measures on one axis is done by dragging each measure to a single axis. Pointing at the cost axis in the upper left makes a green fold mark appear. Pointing at the green fold makes a cross arrow appear. Using the green fold you can drag the cost axis on top of the sales axis.
Repeating the same method, seen in Below Diagram, you can relocate the gross margin axis so that all three measures are displayed together on a single axis. Note in Below Diagram
That the row shelf now holds the measure values pill. Measure names are on the marks card for color. The measure values shelf also appears, displaying all three measures. Tableau automatically creates the measure names and measure values pills when a second measure is moved to the same axis. If you are wondering how the labels were made to appear at the beginning and end of each line, explore the label button on the marks card.
Diagram of Dragging the cost axis to the sales axis
Measure names and measure values help you understand and present your data set. They help you contend with poorly formatted data, and facilitate creating more advanced chart types. Many beginning and intermediate Tableau users avoid these specific fields because they are unfamiliar. Spend a little time working with them. The added flexibility you discover will be worth the effort.
You’ve seen all the ways that Tableau’s built-in chart types, trend lines, reference lines, groups, sets, sorting, and filtering help you add meaning to your data within Tableau. In the next chapter you’ll learn how calculations provide even more flexible ways to add information to your charts and dashboards.
Enroll for Instructor Led Live Tableau Training