Combining Tableau Start and End Dates into a Single Axis

Suppose you want to show marks in a view that represent aggregated values over time, but your data only contains start and end date values. Because two date fields cannot be used on a single axis, you can reshape your data by creating a table that contains values between the start and end date and then join the new table with the original data.

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 Name	Start	End	Monthly Burn Totals
A	1/1/2012	1/1/2013	65656.67
B	9/1/2012	9/1/2013	160000.00
C	1/1/2012	1/1/2013	50000.00
D	6/15/2012	6/15/2012	1500.00
E	9/1/2012	2/1/2013	20000.00

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/2012
2/1/2012
3/1/2012
4/1/2012
5/1/2012
6/1/2012
7/1/2012
8/1/2012
9/1/2012
10/1/2012
11/1/2012
12/1/2012
1/1/2013
2/1/2013
3/1/2013
4/1/2013
5/1/2013
6/1/2013
7/1/2013
8/1/2013
9/1/2013

After you create a table like this, you can use an outer join to join the original data and the table above. Then you can use Tableau to show the monthly burn values based on the dates on the list you created.    

Step 1

Open Microsoft Excel and create a new worksheet.

Step 2

Type Master Date in the first field, and then type the following values in the subsequent fields below it.



Note: If your data is stored in a database, create a new database table with a field called “Master Date” and add the list of dates above.

Step 3

Open Tableau Desktop, and connect to the Excel data source you created in step 2.

Step 4

In the Excel Workbook Connection dialog box, select Multiple Tables and then the Add Table button. 

Step 5

In the Add Table dialog box, click the Join tab, and then select the type of join from the Join Type drop-down list. In this case, select Right.

Step 6

Under Add Join Clause, create the following join clause:

Contract Start Date <= Master Date

Contract End Date >= Master Date

This join clause will display the following join clauses under Join Clause:

[‘Original Date$’].[Contract Start Date]<=[‘Master Date$’].[Master Date]

[‘Original Date$’].[Contract End Date]>=[‘Master Date$’].[Master Date]
 

 

The join clauses tells Tableau to combine the start and end date values with the date values in the Master Date table. More specifically, the clauses above joins Contract Start Date values less than or equal to the Master Date values and Contract End Dates values greater than or equal to the Master Date values to each Master Date value.

Step 7

When finished, click OK to close each dialog box.

Step 8

Click Connect Live or Import all data.

Step 9

Drag the ‘Master Date$’_Master Date field to the Columns Shelf.

Step 10

Right click the ‘Master Date$’_Master Date field on the Columns shelf and select Exact Date.

Step 11

Drag the Monthly Burn field to the Rows shelf. Now you can see the burn totals across all contracts over time.