Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (17.6k points)

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:  

Ideal visualization

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:

1st approach

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:

enter image description here

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.

2nd approach

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:

enter image description here

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?

closed

1 Answer

+1 vote
by (47.2k points)
selected by
 
Best answer

Second approach is close. Place StartDate as continuous instead of sum(datediff) on the columns. Then place sum(datediff) on size. You can now sort Company ascending on sum(datediff). Sometimes, reorganizing data is necessary.

enter image description here

Related questions

0 votes
1 answer
asked Jul 29, 2019 in BI by Ashok (47.2k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Browse Categories

...