Tableau Desktop Fundamental Tutorial 5: Slicing Your Data By Date

A. Working With Dates in Tableau

For relational data sources, Tableau automatically places dates and times in the Dimensions area of the Data window. Date/time fields are identified by the date and time icon as shown below.

NOTE: Date hierarchies and attributes in OLAP (or cubes) are defined when the cube is created and you cannot modify them in Tableau

When you place a date on a shelf, the field name automatically reflects the default date level, defined as the highest level of multiple data instances. For example, if the data field includes multiple years, the default level is year. However, if the date field contains data for just one year but includes multiple months, then the default level is month.

1. Change a Date Level for a Date Field

Drag a date field into the view on the Rows or Columns shelf.
Click the drop-down arrow on the date field, and select the date/time level (year, quarter, month, week, day, hour, and so forth).

B. Understanding and Using Discrete and Continuous Dates

Continuous dates represent the natural, chronological progression of time. When you add a continuous date/time dimension to view, Tableau creates an axis as a time line. Use continuous dates when you want to show your data organized along a timeline, from the first data point in time to the last data point time.

In contrast, when a discrete date dimension is added to the view, Tableau organizes the dates as discrete parts, independent of linear time. Use discrete dates to show your data organized by date units, such as aggregated data for all Novembers over several years.

These different time behaviors create different views. If the date/time attributes of your view do not seem right for your data, you might need ti change your date dimension to discrete or continuous.

1. Change Date Dimension from Discrete to Continuous, and Continuous to Discrete.

To make a date dimension continuous by default:

Right click the field in the data window and select Convert to Continuous. Note that the field icon turns green and converts to a continuous field when added to a shelf.

To revert to discrete:

Right click the field in the data window and select Convert to Discrete

To make a date dimension continuous in a view:

Drag the date field to columns or rows.
Click the dropdown menu, and choose the date unit you want to use from the continuous date levels

Change Date Dimension from Discrete to Continuous, and Continuous to Discrete

C. Using Discrete Date Parts

You can move discrete date parts to different shelves or to a nonlinear order.

1. Reorder the Date Hierarchy

You can order discrete dates according to any hierarchy that produces the view you need. The view below shows quarter before of order date, which allows a user to analyze quarterly sales trends over years.

a) Change Hierarchical Order o Date Fields

Drag a date field into the view to the Rows or Columns shelf.
Drag a measure field (for example, sales) row.
Click the plus (+) sign in the date field to expand the date hierarchy
Drag field around on the rows/columns shelves to reorder the hierarchy. The resulting view serves a different analytical purpose.

2. Different Date Parts on Different Shelves

You can also place different date parts on different shelves. In this view, the Year of Order Date is on the Column shelf and the quarter of Order Date on the Row shelf.

Tableau Different Date Parts on Different Shelves

a) Place date parts on different shelves

Drag the discrete date field into the view, and click the plus (+) sign to drill down.
Select one of the expanded date parts, and move it to a different shelf.

D. Practice: Discrete and Continuous Dates

1. Discrete Month View

From the Starter folder on your USB, open the package workbook named Using Discrete_and_Continuous_Dates_Starter.twbx and use Sheet 1 to create a view according to the following specification:

Uses bars to show the aggregation of data by month.

2. Direction Details

Create a view for Discrete Dates:

Right click the order date on columns and select the discrete month format.
Note that the plus sign on the date on columns allows you to drill down further into discrete date and you can also use the minus sign to move back to less detailed levels of detail.

Note that Tableau nests every new level in the hierarchy as a discrete dimension.

3. Continuous Month View

Use Sheet 2 of Using Discrete_and_Continuous_Dates_Starter.twbx to create a view according to the following specification:

Dates are shown in a continuous time line at the month level.

Drag sales to rows and order date to columns
Format the order date on columns as continuous month

NOTE: The field for (Order Date) is now green, indicating it is continuous, and the axis is converted to continuous. While it is possible to change from discrete to continuous in the right click menu, that method will not enable the drill options needed below.

On the order date field, use the plus sign to drill down to lower level of detail. Then use the right click menu return to continuous month.

NOTE: The differences between a discrete and a continuous dill: Tableau replaces the previous continuous date value with the new one, and there is no option to drill back up to higher levels (“-“). However, you can choose a higher date level from the context

menu.

5. Solution

a) Discrete Months View

Download this file Discrete_and_Continuous_Dates_Starter.twbx
Verify that sheet 1 is selected.
From measures in the Data window, drag sales to row.
From dimensions in the Date window, drag Order Date to Columns.
On the Columns shelf, right click the order date field and select the discrete month format (May), as shown below.

On the Marks card, click the mark type deop down list ad change it from automatic to bar.

b) Continuous Month View

Select Sheet 2
From measures in the data window, drag sales to rows.
From dimensions in the date window, drag Order Date to columns
On the Columns shelf, right click the order date fields and select the continuous month format (may 2011), as shown below.

E. Defining a Fiscal Year

You can change the default properties of a date/time field to display according to your organization’s fiscal date equivalent. For instance, calendar year always run from January 1st until December 31st. However, a company’s fiscal year might run from July 1st in one year through June 30th of the following year. The start of the fiscal year has to be the first of the month.

1. Define a Date by Fiscal Year

Right click the date dimension in the data window, select Default Properties, and choose Fiscal Year Start
On the context menu, choose the starting month of the fiscal year.

F. Creating Custom Dates

Custom dates are calculated date parts you can save in the Data window, and can be Discrete (Date Parts) or Continuous (date values). Create a custom date so you can save and reuse the custom date part or value without having to override the default behavior for each use of the date.

Using custom dates also breaks the automatic date hierarchy tableau creates and allows for each field to represent just itself – similar to a DATETRUNC() function.

1. Create a Custom Date

In the data window, right click on the date field you want to use for the custom date, and choose Create Custom Date.
On the Create Custom Date dialog box, in the Name field, name the new custom date field.
From the Detail drop down list, select the date type you want to use.
Choose Part to set the date as discrete, and Date value to set the date as continuous.

NOTE : Options not available for a date type appear grayed out

Click OK

G. Practice: Custom Dates

From the starter folder using your USB, open the package workbook named Custom_Dates_Starter.twbx and creates two views according to the following specifications:

1. Continuous Custom Date

Shows a line chart of sales by order date.
Use custom continuous dates shown in quarters.

2. Direction Details

Create a custom date with a value of quarters, and name it “Order Date (Continuous Quarters)”.
Drag the newly created order date (continuous quarters) to column on top of the month field to replace it entirely.

3. Discrete Custom Date

Shows a bar chart of sales by order date.
Uses custom discrete dates shown in quarters.

4. Direction Details

Create a custom date with date value of quarters, changed to Date Part, and name this field “Order Date (Discrete Quarters)”.
Remove the date field on the columns shelf, and drag this new field to column.
Change the mark type to bar.

5. Solution

a) Continuous Custom Dates

From the Starter folder open the workbook named Custom_Dates_Starter.twbx and select Sheet 1.
In the Dimensions area of the Data window, right-click the Order Date field, and choose Create Custom Date.
In the Create Custom Date dialog box, name the custom date "Order Date (Continuous Quarters)", select Quarters from the Detail drop down list, verify that Date Value is selected, and click OK.
Drag the newly created Order Date (Continuous Quarters) to Columns on top of the Month field to replace it.

b) Discrete Custom Dates

• Switch to Sheet 2 of Custom_Dates_Starter.twbx.

• In the Data window, right-click on the Order Date field, and choose Create Custom Date.

• In the Create Custom Date dialog box, name the custom date "Order Date (Discrete Quarters)", selects Quarters from the Detail drop down list, select Date Part, and then click OK.

• Drag the newly created Order Date (Discrete Quarters) to Columns, and remove the other instance of Order Date from Columns.

• On the Marks card, change the mark type to bar.

H. Practice: Using Missing Values to Show Gaps in Data

From the Starters folder open Showing_Gaps_in_Data_Starter.twbx and use Sheet 2 to create a chart according to the following specification:

Create the following view by showing Date as Discrete Day, including missing values, and formatting null values:

Tableau Using Missing Values to Show Gaps in Data

1. Direction Details

Create the following view on Sheet 1.

For the DAY (Date) field, choose to show missing values.

There are now line breaks due to the new headers for on the horizontal axis

Change the setting for SUM (Sales) to "Show at Default Value."
Try the other options in this drop-down list and observe the differences in the view.

2. Solution

a) To Show missing values

From the Starters folder, open showing_gaps_in_data_starter.twbx.
On Sheet 1 note that the crosstab only has data for weekdays.
Switch to Sheet 2.
From Measures, drag Sales to Rows.
From Dimensions, drag Date to Columns.
On Columns right click YEAR (Date) and select the discrete day format (8).

On Columns, on the DAY(Date) field, click the drop-down list and choose Show Missing Values.

b) To Change how missing values are displayed

Right-click on the Sales axis and choose Format.
On the Pane tab, under Special Values, change the Marks drop-down list setting to Show at Default Value.
In the same drop down list, select Hide (Connect Lines).
In the same drop down list, select Hide (Break Lines).

NOTE: The Solution workbook shows the results when stopping at step 2 above