KNOWLEDGE BASE

Combining Multiple Date Fields


Published: 24 Apr 2017
Last Modified Date: 15 Oct 2018

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 both options.
CLICK TO EXPAND SOLUTION
Option 1: Pivot the Date Fields
  1. 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
  2. Right-click and drag [Pivot Field Values] to the Rows shelf
  3. In the Drop Field dialog, select MY(Pivot Field Values) and click OK
  4. Right-click [Pivot Field Names] in the data pane and select Aliases…
  5. In the Edit Aliases dialog, give a more descriptive alias to Order Date and Ship Date and click OK
  6. Drag [Pivot Field Names] to the Columns shelf
  7. Right-click and drag [Order ID] to Text on the Marks card
  8. 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.
  1. Choose one date field to create the date axis or headers. This example uses [Order Date]
  2. Right-click and drag [Order Date] to the Rows shelf
  3. In the Drop Field dialog, select MY(Order Date) with the blue # icon and click OK
  4. Right-click and drag [Order ID] to Text on the Marks card
  5. In the Drop Field dialog, select CNTD(Order ID)
  6. Right-click the header "Distinct count of Order ID" in the view and select Edit Alias…
  7. In the Edit Alias dialog, type in a new name and click OK
  8. 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.
  1. 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"
  2. Navigate to the Data source tab in Tableau Desktop
  3. Add the "Sheet 1" table to the canvas area
  4. 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 >=
  5. Select Analysis > Create Calculated Field
  6. 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
      )
  7. 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
    )
  8. Right-click and drag [Date] to the Rows shelf
  9. In the Drop Field dialog, select MY(Date) and click OK
  10. Drag [Orders Placed] to Text on the Marks card
  11. 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.
  1. 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)"
  2. 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.
  3. Navigate to Data > Edit Relationships…
  4. 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…
  5. 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
  6. 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)
  7. Click OK to close the Relationships dialog
  8. In a new worksheet, right-click and drag [Date] from the "Sheet1 (master date list)" data connection into the view
  9. In the Drop Field dialog, select MY(Date) and click OK
  10. Right-click the header "Distinct count of Order ID" in the view and select Edit Alias…
  11. In the Edit Alias dialog, type in a more descriptive name and click OK
  12. Right-click and drag [Order ID] from the "Sample - Superstore (link on order date)" data source onto Text on the Marks label
  13. In the Drop Field dialog, select CNTD(Order ID) and click OK
  14. Right-click and drag [Order ID] from the "Sample - Superstore (link on ship date)" data source onto Text on the Marks label
  15. In the Drop Field dialog, select CNTD(Order ID) and click OK
  16. Drag CNTD(Order ID) from Detail on the Marks card over the numbers in the view and drop the field
  17. 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
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]
Did this article resolve the issue?