I'm working with a dataset that describes contract durations with a list of companies. Obviously, all contracts have a start date and a (minimum) duration. Some contracts, however, are subject to possible prolongation. Here's a data example:
Company contractStart minDuration firstProlong secProlong
Company x 27-11-2011 27-11-2014 27-11-2015 27-11-2016
Company y 29-11-2014 29-11-2015
Company z 01-04-2011 01-12-2011 01-12-2015 01-12-2017
I want to visualize them in a Gantt'ish type diagram that has duration along the x-axis and shows the respective contract lengths with the possible prolongations as a sort of stacked bar chart. Along with that, I want something to mark the current date. I've managed to do this in Excel. See picture:
I would however really like to be able to the same thing in Tableau, and I really can't figure out how to. I've been working with two different approaches so far:
Here I drag the Company to the Rows shelf and the contractStart date to the Columns shelf. I then create a calculated field that calculates (with DATEDIFF) the difference in days between the contract start and the minimum duration. I can drag that to the Size shelf, set the mark type to Gantt bar, and get something like this:
I can then go on to calculate the date difference between the minimum duration and the 1st prolongation and the same for the 1st and 2nd duration. The problem here is that I can't add these calculated fields to the Size shelf as it can only hold one at a time.
Here I reorganize my data like this:
Company Type startDate endDate
Company x minDuration 27-11-2011 27-11-2014
Company x firstProlong 27-11-2014 27-11-2015
Company x secProlong 27-11-2015 27-11-2016
Company y minDuration 29-11-2014 29-11-2015
Company y firstProlong
Company y secProlong
Company z minDuration 01-04-2011 01-12-2011
Company z firstProlong 01-12-2011 01-12-2015
Company z secProlong 01-12-2015 01-12-2015
I then create a calculated field to find the difference in days between startDate and endDate. I drag that to the Columns shelf and drag Company to the Rows shelf. I then drag Type to the Color shelf, set the mark type to Bar, and get something like this:
The problem here is that I need dates along the x-axis, not integers. Also, I need to be able to sort the chart like my initial Excel example. I.e. I want to sort on the longest possible duration of a contract (ascending). Also having to reorganize all my data seems a bit tedious.
Here are the spreadsheet and the tableau file I've been working with. Am I overlooking something really simple?