Combining Start and End Dates into a Single Axis

Published: 24 Apr 2017
Last Modified Date: 23 Oct 2017


How to show values over time when your data only contains start and end date values. 

For example, suppose you have several contracts whose start and end date values are different from each other. You want to create a view that shows the monthly burn totals across all contracts over time, despite when the contracts start and end.

Contract NameStartEndMonthly Burn Totals




Tableau Desktop


To create a view that shows the aggregated values based on the start and end date values listed above, you have to create a second table, using Microsoft Excel for example, which lists the date values in between. In this contracts example, the list looks like this:

This example uses the attached CombiningStartAndEndDate.xslx
  1. If using an Excel file, create a new worksheet. Enter Master Date for a header, then add the above list of dates. 
    If using a relational database, create a new table with a field called "Master Date" and containing the list of dates. 
    Note: In the attached example, the Master Date worksheet has already been created for you. 
  2. Open Tableau Desktop and select Connect To a File > Excel
  3. Select CombiningStartAndEndDate.xlsx, click the drop-down arrow next to Open, and then select Open with Legacy Connection.
  4. Drag Contracts to the join area. 
  5. Drag Master Date to the join area. 
  6. in the Join dialog box, select the Right join type, and enter the following two join clauses: 
    • Start <= Master Date
    •  End >= Master Date
  7. Click the sheet tab. 
  8. Drag Master Date to Columns. 
  9. drag Monthly Burn Totals to Rows. 
The view now shows the burn totals across all contracts over time. 

Additional Information

The Legacy Connection is required to complete these steps in Excel, because the standard Tableau Excel connection does not allow the necessary join clauses. 
In order to use the Legacy Connection, it is necessary to create a new connection to your Excel file and select Legacy Connection. An existing Excel connection cannot be converted to a Legacy Connection. 
Did this article resolve the issue?