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.

f:id:skn_0410:20160805081912p:plain

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

Like this.

f:id:skn_0410:20160805082211p:plain

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.

f:id:skn_0410:20160805083556p:plain

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

[End Time - Start Time]

[End Time]-[Start Time]

 

f:id:skn_0410:20160805083757p:plain

 

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.

f:id:skn_0410:20160805085224p:plain

 

f:id:skn_0410:20160805085250p:plain

 

f:id:skn_0410:20160805085401p:plain

 

Now we got these data structure as a starting point.

f:id:skn_0410:20160805085509p:plain

 

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.

f:id:skn_0410:20160805091210p:plain

f:id:skn_0410:20160805091248p:plain

 

f:id:skn_0410:20160805091750p:plain

 

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

 f:id:skn_0410:20160805092407p:plain

Then add table calc of "Running_sum"

f:id:skn_0410:20160805092504p:plain

Add category as color.

f:id:skn_0410:20160805092645p:plain

 

And Here is an easy Trick!

"Show Missing Value" on Column.

f:id:skn_0410:20160805092935p:plain

==>

f:id:skn_0410:20160805093014p:plain

Here is a "GANTT"-like Bar Chart.

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

f:id:skn_0410:20160805093308p:plain

Others are just small modifications.

f:id:skn_0410:20160805093532p:plain

 

f:id:skn_0410:20160805093617p:plain

 This is the last picture of combined chart.

f:id:skn_0410:20160805093731p:plain

 

Do you like that?

Enjoy Tableau !!

 

public.tableau.com