Customized Gantt Chart "Individual + Summary"

Here in Mid West/North U.S., usually we can enjoy pretty fine weather in this season.

Blue Sky and NO clouds.

However, becasue of too dry air, wild fire often happens. Then depend on the wind direction, smoke comes around the area and it smells. Hope the firefighters put out the fire soon and smoke goes away.


I got a good solution for the issue on title.

I had a gantt chart like this.


and at the same time, i want to show how many "categories" are "ON" at specific time slot.

Like this.


I can combine these charts on dashboard, but it's quite troulesome from the axis alignment persptctive.

The target is that how to combine individual Gantt chart and summary line /bar chart together in one sheet or completely aligned dashboard.


The data set:

Original data looks like below.


This is appropriate format to create Gantt chart with below logic.

[End Time - Start Time]

[End Time]-[Start Time]




However, we're in trouble when we try to create Bar chart to count items of "ON" status.

The reason is below.

To create Bar Chart with continous filed, we need to define one axis.

That should be either "Start Time" or "End Time".

In eitehr case, the mimimum "Start Time" is earlier than minimum "End Time" and maximum "End Time" is later than maximum of "Strat Time" from the given fact.

So in any case, the first plot or the last point must be missed.

What is needed first?

That's data pivot.







Now we got these data structure as a starting point.



Now Create Gantt Chart Again


This data strucure is much easier to handle than original GANTT chart.

However, we lost the relatioinships for same event's Start time and End time.

But as longs as we have original data source, we can make relationships between two source with linking "Category" and "Start time".

And we can get end time from original data source.






But we can be more creative with new data set.

We can create Gantt Chart-like chart

Create calculated field like below.

[Data for new Chart]

if [Pivot field names]="Start Time" then 1
elseif [Pivot field names]="End Time" then -1
else 0 end


Then add table calc of "Running_sum"


Add category as color.



And Here is an easy Trick!

"Show Missing Value" on Column.




Here is a "GANTT"-like Bar Chart.

Then, it's easy to add Column Total on the bottom.


Others are just small modifications.




 This is the last picture of combined chart.



Do you like that?

Enjoy Tableau !!