Combining Multiple Date Fields

Published: 24 Apr 2017
Last Modified Date: 09 Apr 2024

Question

How to combine multiple date fields into a single axis, or a single date header.

For example, every order has a [Order Date] and a [Ship Date]. The end goal is to either show a line chart or a crosstab of the number of orders placed and shipped on each month. If [Order Date] is used as the axis or the header, then the number of orders shipped each month will be incorrect, and vice versa.

Environment

Tableau Desktop

Answer

The attached example workbook uses the sample data set Superstore to demonstrate all options. Sample workbook can be downloaded from the Attachments section on the right.

CLICK TO EXPAND SOLUTION

Option 1: Pivot the Date Fields

Pivot the date fields, so that there is only one field with date data, and a second field like "Event" that describes whether the event is "order placed" or "order shipped" Note: for some data sources, pivoting can be done directly in Tableau Desktop. For detailed directions, see Pivot Data from Columns to Rows
Right-click and drag [Pivot Field Values] to the Rows shelf
In the Drop Field dialog, select MY(Pivot Field Values) and click OK
Right-click [Pivot Field Names] in the data pane and select Aliases…
In the Edit Aliases dialog, give a more descriptive alias to Order Date and Ship Date and click OK
Drag [Pivot Field Names] to the Columns shelf
Right-click and drag [Order ID] to Text on the Marks card
In the Drop Field dialog, select CNTD(Order ID) and click OK

CLICK TO EXPAND SOLUTION

Option 2: Use an LOD Calculation

This option requires that every date field have at least one record for every date bin in the view. In this example, every row is a month, therefore both [Order Date] and [Ship Date] need to have at least one order for every month.

Choose one date field to create the date axis or headers. This example uses [Order Date]
Right-click and drag [Order Date] to the Rows shelf
In the Drop Field dialog, select MY(Order Date) with the blue # icon and click OK
Right-click and drag [Order ID] to Text on the Marks card
In the Drop Field dialog, select CNTD(Order ID)
Right-click the header "Distinct count of Order ID" in the view and select Edit Alias…
In the Edit Alias dialog, type in a new name and click OK
Create a calculated field with a name like "Orders Shipped" with a calculation similar to the following:

MIN( IF DATETRUNC('month', [Order Date]) = DATETRUNC('month', [Ship Date]) THEN { FIXED DATETRUNC('month', [Ship Date]) : COUNTD([Order ID]) } END )

9. Double-click [Orders Shipped] in the data pane to add the field to the view

CLICK TO EXPAND SOLUTION

Option 3: Use Date Scaffolding

This option requires that one date field always come before another date field, and works best when the two dates fields are defining a range.

Create a table that contains a master list of all possible dates, which will be joined to the original data connection. In this example, that table is called "Master Date List"
Navigate to the Data source tab in Tableau Desktop
Add the "Sheet 1" table to the canvas area
In the Join dialog, so the following:
1. Select Left
2. Under Data Source, select Order Date
3. Under Sheet 1, select Date
4. Click the equal sign, and select <=
5. Under Data Source, click Add new join clause, and select Ship Date
6. Under Sheet 1, select Date
7. Click the equal sign, and select >=
Select Analysis > Create Calculated Field
In the Calculated Field dialog box that opens, do the following, and then click OK:
1. Name the calculated field. In this example, the calculated field is named "Orders Placed"
2. In the formula field, create a calculation similar to the following:
  COUNTD( IF DATETRUNC('month', [Order Date]) = DATETRUNC('month', [Date]) THEN [Order ID] END )
Create a calculated field with a name like "Orders Shipped" with a calculation similar to the following:
COUNTD( IF DATETRUNC('month', [Ship Date]) = DATETRUNC('month', [Date]) THEN [Order ID] END )
Right-click and drag [Date] to the Rows shelf
In the Drop Field dialog, select MY(Date) and click OK
Drag [Orders Placed] to Text on the Marks card
Double-click [Orders Shipped] in the data pane to add the field to the view

CLICK TO EXPAND SOLUTION

Option 4: Use Data Blending

This option will not work for a range of dates.

Create a table that contains a master list of all possible dates, and create a new connection to that list. In this example, that connection is called "Sheet1 (master date list)"
Navigate to Data > Sample - Superstore > Duplicate. In this example, I have named the two copies of "Sample - Superstore" as "Sample - Superstore (link on order date)" and "Sample - Superstore (link on ship date)" for clarity.
Navigate to Data > Edit Relationships…
In the Relationships dialog, do the following:
1. For Primary data source, select Sheet1 (master date list)
2. For secondary data source, select Sample - Superstore (link on order date)
3. Select the Custom radio button
4. click Add…
In the Add/Edit Field Mapping dialog, do the following:
1. Click the arrow next to Date to expand the date options
2. Select the date level that matches the most granular date level used in the view. In this example, the view uses MY(Date).
3. In the right pane, click the arrow next to Order Date
4. Select the matching date level for Order Date
5. Click OK
Repeat steps 4-5 with "Sample - Superstore (link on ship date)" as the secondary data source, and adding a relationship on MY(Date) = MY(Ship Date)
Click OK to close the Relationships dialog
In a new worksheet, right-click and drag [Date] from the "Sheet1 (master date list)" data connection into the view
In the Drop Field dialog, select MY(Date) and click OK
Right-click the header "Distinct count of Order ID" in the view and select Edit Alias…
In the Edit Alias dialog, type in a more descriptive name and click OK
Right-click and drag [Order ID] from the "Sample - Superstore (link on order date)" data source onto Text on the Marks label
In the Drop Field dialog, select CNTD(Order ID) and click OK
Right-click and drag [Order ID] from the "Sample - Superstore (link on ship date)" data source onto Text on the Marks label
In the Drop Field dialog, select CNTD(Order ID) and click OK
Drag CNTD(Order ID) from Detail on the Marks card over the numbers in the view and drop the field
In the Edit Alias dialog, type in a more descriptive name and click OK

Additional Information

Notes on Option 2 (Level of Detail expression):

Every date field must have at least one record for the date level used in the view
The FIXED expression will return the total distinct count of shipped orders for each month in [Ship Date] regardless of where that month of [Ship Date] appears in the view. For example, orders shiped in February where placed in both January and February. Thus, the FIXED expression by itself would return 59 orders in both MY(Order Date) = January and MY(Order Date) = February.
The whole IF statement is wrapped in MIN() to avoid an issue where LOD expressions will return duplicated values inside of an IF statement.

Notes on Option 3 (date scaffolding):

It is also possible to list all dates at a higher date level, such as the first day of each month. Then in steps 4-2 and 4-5 use a join calculation that will set the date in the original table to the correct date level, such as DATETRUNC('month', [Order Date])
If it is not possible to add a table to the original data source with all dates, then create a cross-database join to an external table containing all dates. This requires upgrading to Tableau Desktop 10.5 or later to make use of the inequalities in join clauses in cross-database joins feature.
This option will duplicate the data, which may impact other views using the same data source
Starting in Tableau Prep 2021.3.1, it is possible to create date scaffolding in Tableau Prep without creating a Date List. See Fill Gaps in Sequential Data for directions

Notes on Option 4 (data blending):

Data blending has many limitations. For help with potential issues, please see Troubleshoot Data Blending
The dates being linked on must match exactly at the most granular date level selected in the Relationships dialog
When creating relationships between fields of different data sources, each field can only be associated with one other field at a time, which is why it was necessary to duplicate the original data source
Any fields associated with [Order Date] must be pulled from the copy of the original data source linked on MY(Date) = MY(Order Date), and vice versa for any fields associated with [Ship Date]

Displaying Active Records Over Time
Blend Your Data
Create Level of Detail Expressions in Tableau
CASE and IF Statements Containing Level of Detail Expression Return Unexpected Results