Last Modified Date: 16 Aug 2016
Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.
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|
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:
After you create a table like this, you can use an right 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.
Open the contracts Excel file, and create a new worksheet.
Type "Master Date" in the first field, type the following values in the subsequent fields below it, and then save the workbook.
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.
Open a new Tableau Desktop workbook, and then do one of the following;
- For Tableau Desktop 8.3 and earlier, click Connect to Data > Microsoft Excel.
- For Tableau Desktop 9.0 and later, click Excel.
In the Open dialog box, do one of the following:
- For Tableau Desktop 8.1 and earlier, find and select the Excel workbook with both the contracts and master date data, and then click Open.
- For Tableau Desktop 8.2 and later, find and select the Excel workbook with both the contracts and master date data, click the drop-down arrow next to Open, and then select Open with Legacy Connection.
For Tableau Desktop 8.1 and earlier, in the Excel Workbook Connection dialog box, select Multiple Tables and then the Add Table button.
For Tableau Desktop 8.2 and later, on the Data Source page, drag the contracts table from the left-pane to the join area.
For Tableau Desktop 8.1 and earlier, 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.
For Tableau Desktop 8.2 and later, drag the master date table to the join area. The Join dialog box will open.
- For Tableau Desktop 8.1 and earlier, under Add Join Clause, create the following join clause:
Start Date <= Master Date
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]
- For Tableau Desktop 8.2 and later, select the Right join type, and then from the three drop-down fields, create the two join clauses:
Start <= Master Date
End >= 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.
For Tableau Desktop 8.1 and earlier, click OK to close each dialog box, and then complete the connection.
For Tableau Desktop 8.2 and later, click the sheet tab.
Drag the ‘Master Date$’_Master Date field to the Columns Shelf.
Right click the ‘Master Date$’_Master Date field on the Columns shelf and select Exact Date.
Drag the Monthly Burn field to the Rows shelf. Now you can see the burn totals across all contracts over time.