KNOWLEDGE BASE

Combining Start and End Dates into a Single Axis


Published: 24 Apr 2017
Last Modified Date: 28 Aug 2017

Question

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
A1/1/20121/1/201365656.67
B9/1/20129/1/2013160000.00
C1/1/20121/1/201350000.00
D6/15/20126/15/20131500.00
E9/1/20122/1/201320000.00

 


 

Environment

Tableau Desktop

Answer

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:

1/1/20128/1/20123/1/2013
2/1/20129/1/20124/1/2013
3/1/201210/1/20125/1/2013
4/1/201211/1/20126/1/2013
5/1/201212/1/20127/1/2013
6/1/20121/1/20138/1/2013
7/1/20122/1/20139/1/2013
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?