Subscribed unsubscribe Subscribe Subscribe

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

 

 

 

 

 

 

 

 

 

Who purchased both Product A and Product B

I got smilar questions couple of times regarding the issue I stated as title.

The below link is a case "Akiyah" tried to create VIZ with 4 calculated fields with using LOD calculations.

akiyah.hatenablog.com

But as he staetd in the contents, it's little bit troublesome to create 4 fields.

So, I will propose different approach.

 

First of all, create new calculated field to categorize customers.

 

Create Calculated Field 

[Customer Category]

if
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}>0
and
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}>0
then
"1-Purchased both "+[Param 1]+" and "+[Param 2]

elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}
=
{fixed[Customer Name],[Order month] :sum([Sales])}
then "2-Purchased "+[Param 1]

elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}
=
{fixed[Customer Name],[Order month] :sum([Sales])}
then "3-Purchased "+[Param 2]

ELSE
"4-No purchase on "+[Param 1]+" nor "+[Param 2]
END

 

Little bit long calculation??  That's right. I add some explanations.

[Customer Category]

if
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}>0 // in same month, same customer, sum up Param 1's sales and judge it is gerater than 0

and
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}>0 // in same month, same customer, sum up Param 2's sales and judge it is gerater than 0
then
"1-Purchased both "+[Param 1]+" and "+[Param 2] // if both of above two conditions are met, the text shows up.


elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)} // in same month, same customer, sum up Param 1's sales
=
{fixed[Customer Name],[Order month] :sum([Sales])} // in same month, same customer, sum up ALL sales
then "2-Purchased "+[Param 1] // in case above two matches each other, the text shows up.  that means only showing sales when only Param-1 are purchased.

elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)} // in same month, same customer, sum up Param 2's sales
=
{fixed[Customer Name],[Order month] :sum([Sales])} // in same month, same customer, sum up ALL sales
then "3-Purchased "+[Param 2] // in case above two matches each other, the text shows up.  that means only showing sales when only Param-2 are purchased.

ELSE
"4-No purchase on "+[Param 1]+" nor "+[Param 2] // all of other cases than above
END

 The reason I add 1 to 4 on top of text is forcing to sort the category as alphabetical sorting, kind of TIPS, bit essential.

We put this calculated field on color shelf and get the view. Plus put this field on Label as well and we are able to see the category without color legend.

The benefit of this approach is that we can show the category name directly rather than show label by parameter name.

Here is the VIZ.

f:id:skn_0410:20160701062004p:plain

Yet, we feel a little bit unsatisfied ????

Because we want to highlight the category of "1- purchased both", but the VIZ does not meet that needs well.

So, additional works needed.

 

1. Highlight by Color

Emphasize "1- purchased both" means less emphasize others, then we change the color littel but lighter.

f:id:skn_0410:20160701054407p:plain

Double click on the red color of the category of "1- purchased both".

Then pick darker color.

f:id:skn_0410:20160701054550p:plain

f:id:skn_0410:20160701054723p:plain

f:id:skn_0410:20160701054824p:plain

Please ignore the preference of color for now, anyway, getting better.

2. Highlight by Label

 It's not bad, but we are always greed to Tableau and want to emphasize the label as well.

We want to make one category's lable size larger.

This is not doen thru standard function and need some trick.

To make that happen, we need to create two more calculated fields only for this purpose. In this case, the formula should be very easy.

 

[HighLight]

if [Customer Category] = "1-Purchased both "+[Param 1]+" and "+[Param 2]
then [Customer Category] end

 

[Non-HighLight]

if [Customer Category] <> "1-Purchased both "+[Param 1]+" and "+[Param 2]
then [Customer Category] end

 

Put both fields on Label shelf

f:id:skn_0410:20160701055404p:plain

and open detail setting window.

f:id:skn_0410:20160701055845p:plain

Remove break line, select only <HighLight>, and change the format like below.

f:id:skn_0410:20160701060001p:plain

We can change the format of respective field when we put multiple fields in same label.

Plus, in the formula, we don't put else statement which automatically set "null".

The "null" does not show up anywhere and does not show up as "blank" neither.

Then, even in the case that we put multiple fields in same field, the label is not duplicated, provided the condition restricts to show only one value in one of these fields.

 

This is the final VIZ.

f:id:skn_0410:20160701080218p:plain

 

OK, Let's play around and enjoy!!

public.tableau.com

 

 

Different color for multiple categories on bar charts

Hello,

I want to put two categories' color into one bar cahrt.

I mean taht I want to create VIZ like below.

f:id:skn_0410:20160624010320p:plain

Actually there is very eay way to make this happen.

We can drag two dimensions at same time into color shelf.

With that, Tableau automatically select appropriate color setting for all combination of colors.

f:id:skn_0410:20160624010659p:plain

f:id:skn_0410:20160624010748p:plain

f:id:skn_0410:20160624010832p:plain

Done !

 

This is very easy and simple, however, there are some problems.

- Color legend shows 9 colors which is busy.

- When you try to change color feature, you need to change all 9 colors respectively.

- We lose the color we use for existing category. (<== Thsi is the bad side effect.)

   For examle, I have different color set for "Product Category", but this work overwrite whole color settings.

 

So, I will show different method to achieve same result.

Let's start from simple VIZ like below.

I cahnged color set of "Product Category" intentionally.

f:id:skn_0410:20160624011611p:plain

f:id:skn_0410:20160624011714p:plain

We already have "Sales" field in the row shelf, but add one more "Sales" field into secondary axis.

f:id:skn_0410:20160624011814p:plain

Don't forget "Synclonize"

f:id:skn_0410:20160624011857p:plain

We can see two Bar charts settings.

f:id:skn_0410:20160624011956p:plain

Add "Ship Mode" into secondary axis's Color shelf.

f:id:skn_0410:20160624012104p:plain

Got below VIZ.

f:id:skn_0410:20160624013054p:plain

New Color on secondary axis overwrote exisitng primary axis's color.

Select "Edit Colors" on sencondary Axis Bar chart.

f:id:skn_0410:20160624013213p:plain

f:id:skn_0410:20160624013436p:plain

Gray 5 might be appropriate.

f:id:skn_0410:20160624013403p:plain

f:id:skn_0410:20160624014618p:plain

This the light ==> dark order is not appropriate, so we manually change "Delivery Truck" color.

f:id:skn_0410:20160624015020p:plain

f:id:skn_0410:20160624015148p:plain

Then on secondary bar cahrt's color shelf, we change the transparency.

f:id:skn_0410:20160624015307p:plain

25%'s case is as example, but you can choose the rate as you like.

f:id:skn_0410:20160624015440p:plain

Hide secondary axis's header.

f:id:skn_0410:20160624015625p:plain

And add label.

f:id:skn_0410:20160624015718p:plain

We could show "Ship Mode" category by thickness of color with keeping customized "Priduct Category" color.

| Tableau Public

f:id:skn_0410:20160624231129p:plain

 

OK, let's Play around and Enjoy!

 

Show Stacked Bar and Standard Bar Chart side by side

Today's Topic is showing differnt type of Bar chart side by side.

Actullay it's easy enough, but I got some questions from my collegues.

I used Tableau's sample data from the site.

Sample - Superstore Sales (Excel).xls |Tableau Support Community

 

Let's start from this type of view.

f:id:skn_0410:20160623085209p:plain

 

What we need to do are

- Group Low height items 

- Show Stackd Bar cahrt only for Grouped Items.

 

Starting from sort by decending order.

f:id:skn_0410:20160623084522p:plain

 

We got below VIZ.

f:id:skn_0410:20160623085310p:plain

Group the items whose sales are below $500K.

There are multiple methods to do this, but I use LOD formula in this case.

[Province 2]

if {fixed [Province]:sum([Sales])} > 500000 then [Province] else "Small Sales" END

 

The function of "Fixed + Sum" is similar to  EXCEL's "SUMIF" or "SUMIFS"

{fixed [Province]:sum([Sales])} means that nevertheless of other fields value, sum up with field of "Province".

 

Let's put [Province 2] on Column shelf instead of [Province].

We got below VIZ.

f:id:skn_0410:20160623085528p:plain

Becasue we chaged dimension, need to re-sort on [Province2]

f:id:skn_0410:20160623085631p:plain

When we put [Province 2] in color shelf, it looks below VIZ.

f:id:skn_0410:20160623085733p:plain

In Tableau's settnig, Items in column and Items in Label or Color etc are not neccessarily matched easy other.

So, we put [Province]  instead of [Province 2] in color shelf.

Now we can see only [Small Sales] has Stacked Bar with different color.

f:id:skn_0410:20160623090111p:plain

 

Actually, the chart type stays same as "Standard Bar Chart", but with using different dimension as Color, you can show the standard bar chart like stacked.

 

 Rest of small things.

Let's sort color legnend's order.

f:id:skn_0410:20160623090412p:plain

f:id:skn_0410:20160623090456p:plain

And this is completed VIZ.

| Tableau Public 

f:id:skn_0410:20160624091920p:plain

OK, let's Play around and Enjoy!

 

What?? Why Cannot Synchronize Dual Axis

Keep talking about Dual Axis Chart

We want to create the cahrt to compare compnay to company.

 

At first, create the calculated field for comp,any A only sales.

[Sales (=Company A)]

sum(if[Company]="A" then [Sales] end)

 

To create Company B's field just after "A", using duplicate function helps you.

f:id:skn_0410:20160224095622p:plain

f:id:skn_0410:20160224095722p:plain

f:id:skn_0410:20160224095817p:plain

f:id:skn_0410:20160224095852p:plain

Now we have Compnay A's field and Compny B's field respectively.

Now, let's put Company A's field into primary axis and Company B's field into secondary axis.

f:id:skn_0410:20160224100229p:plain

f:id:skn_0410:20160224100332p:plain

f:id:skn_0410:20160224100402p:plain

Next, cahnge company B's field to Line chart.

 

f:id:skn_0410:20160224100502p:plain

f:id:skn_0410:20160224100534p:plain

Then, synclonize seconsary axis. simply use "Edit Axis".

f:id:skn_0410:20160224100619p:plain

f:id:skn_0410:20160224100653p:plain

f:id:skn_0410:20160224100734p:plain

 

Replace Label and change the text format.

f:id:skn_0410:20160224101211p:plain

 

f:id:skn_0410:20160224101305p:plain

f:id:skn_0410:20160224101342p:plain

f:id:skn_0410:20160224101413p:plain

I made it !!

Then Next, Let's compare Company A and other company's average.

Start from creating other Company's averatge field.

 

[Sales (<>Company A)]

sum(if[Company]<>"A" then [Sales] end)/2

f:id:skn_0410:20160224101624p:plain

We put this field on secondary axis with overwritng existing field.

f:id:skn_0410:20160224101713p:plain

We sunclonize secondary Axis.

What ???!!!

 

f:id:skn_0410:20160224101821p:plain.

The check box is grayed out!.

Shall I us "Fixed" value ?? No No, it's not elegant.

I could do it a few minutes ago and why suddenly cannot?

What's the difference??

Yes, it's dicimal

Tableau's each field has own "category" like date , text or number etc.

f:id:skn_0410:20160623102144p:plain

The numebr has two cagegories of Integer and Float.

If the field has dicimal, it's recognized as "Float".

And we cannot synclonize Integer Fields and Float Field.

This is not related to how the fields looks, but related to the content itself.

I mean we need to change the formula itself to align the category.

[Sales (<>Company A)]

int(sum(if[Company]<>"A" then [Sales] end)/2)

Now let's try it again.

f:id:skn_0410:20160224102447p:plain

Great, it's automatically synclonized and check box is also editable.

f:id:skn_0410:20160224102551p:plain

As a take away, when you have trouble to synclonize dula axis, you may have different category field. After cahnging the category of field, you can synclonize the dual axis.

 

OK, Let's play aroudn and Enjoy!

This not what I want : Dual Axis Graph's synchronization

I have not updated several days, let's start.

We want to draw dual axis chart with abar and Graph.

It's not difficult at all, but couple of TIPS.

 Starting from this graph.

f:id:skn_0410:20160223095430p:plain

 

We want to show the delta percentage from last month.

We use table aclculationi, it's easy enough.

f:id:skn_0410:20160223095553p:plain

You drag the table calclation Pill in Rows, then the below graph is available, but not good enough. 

We want to show it as line chart.

f:id:skn_0410:20160223095700p:plain

You can see dotted line when you drag "Difference percentage" pill to right-end of bar chart, which means you can use this field as 2ndary axis.

f:id:skn_0410:20160223095909p:plain

f:id:skn_0410:20160223100526p:plain

It's overwrapped, but bar graph is not good to see.

let's change the type to Line from Bar.

f:id:skn_0410:20160223100659p:plain

f:id:skn_0410:20160223100734p:plain

 

It's done, but somethign is not good.

The reason is Bar chart's main Y-axis include negative numbers.

Only Line chart should have negative number, in this case.

You can change Mian Y-Axis "mimium" as "Zero", but that methos is not "Elegant" at all.

Here is an "Elegant" solution.

f:id:skn_0410:20160223101846p:plain

On 2nd axis, un-check "Include zero".

f:id:skn_0410:20160223101931p:plain

Then ,

f:id:skn_0410:20160223102002p:plain

really done.

We still need to modify lavel . with CTRL, drag "Difference percentage" pill to Line chart's label.

f:id:skn_0410:20160223105503p:plain

f:id:skn_0410:20160223105040p:plain

 

Some lable is overwrapped, but ignorable as long as you show this through Tableau.

If you need to copy paste to PowerPoint, minor making up will be needed, though.

 

ON more final causion:

If you use Main axis to "exclude zero", the graph lokk like below.

f:id:skn_0410:20160223102123p:plain

It seems working, but this graph is not at all correct

Becasue bar graph does not include zero, it starts from 12800, 

This is the first thing we need avoid when we create bar cahrt, please pay attention.

 

K, Let's play aroudn and Enjoy!

 

 

Excel Cannot Achieve (probably) -- Show the difference from Previous Month.

i was asked by my friend. "Teach me the difference from Excel".

........

It's way different, anyway, try to explain with using very simple example.

We use same data which were posted the otehr day.

Tableau View:

f:id:skn_0410:20160213114444p:plain

Excle View:

f:id:skn_0410:20160215233358p:plain

To add lavel, already had problem which mean I needed to add 5 different lable for each.. I gave up to modify the number format x 5 times....

Plus, I could not find a way to add sum of each month on tpo of Bar chart, which we already done in Tableau last week. 

Anyway, up to his point no significant difference.

 

Now we want to show the diference from last month.  Let's do that in both softwares.

 

Excel:

f:id:skn_0410:20160216000233p:plain

f:id:skn_0410:20160216000310p:plain

f:id:skn_0410:20160216000419p:plain

End Results by Excel

f:id:skn_0410:20160216000511p:plain

 

I don't know the way to show vertical ordered two graphs, but only horizontal ordered two graphs.

 

Tableau :

f:id:skn_0410:20160216000842p:plain

f:id:skn_0410:20160216000923p:plain

f:id:skn_0410:20160216000952p:plain

Concept is same as Excel Pivot.

Then drag to Rows.

f:id:skn_0410:20160216001300p:plain

End Results in Tableau.

f:id:skn_0410:20160216001350p:plain

Side by Side (Excel vs Tableau)

f:id:skn_0410:20160216001628p:plain

 

Further more,

I tired to compare each company's difference from previous month.

I even don't want to create something with Excel for this request here....

It requres 3 company x 2 (standard, delta) graphs.....

On the other hadn with Tableau, just drag "Company" on Row.

f:id:skn_0410:20160216002017p:plain

 

Now you can get the Sales by month, diffdrence from previous by Company.

With same scale of "Y-axis" automatically. 

(<== this is important for us, in the past in my company , we wasted time to discuss "BiG' delta of "Small" absolite value.   But if you need to create indivicula chart independently. this is also pretty troublesome.)

f:id:skn_0410:20160216002133p:plain

You can find couple of highlights immediately from these charts.

Company A is pretty stable acroos products, but three data point have big delta.

Company B has big fructuation on Prod.3.

Company C has big fructuation  on Prod.4.

f:id:skn_0410:20160216002345p:plain

 

Furtehrmore, with selecting "Prod.5"(or any other dimension) in color legend, you can highlight "Prod.5" in the graph.

f:id:skn_0410:20160216003021p:plain

Above is just one simple example we don't want to struggle with Escel any more,  : ) 

And Tableau became Savior to me.

<<Link to workbook on Tableau Public>>

Let's play aruojnd and Enjoy!