Example of Water Fall Charts - 2

I wrote Water Fall Chart example yesterday.

It became very long post.

But actually here is much simpler way to create a Water Fall Charts.

f:id:skn_0410:20170210004453p:plain

Using this way, we can create a chart on one worksheet, no need to create dashboard to combine three charts.

This is the huge benefit.

However, we need to use "Union" to triple the data, which is sometimes not available because 

- Union is only available version 9.3 or newer

- Some data connection types does not allow to us Union

- Union might cause the performance issue because of tripled data

In other words, as long as the above conditions ares not concerns, you can enjoy much simpler solution.

OK, let's start with Union.

 

f:id:skn_0410:20170210015926p:plain

f:id:skn_0410:20170210020001p:plain

 

f:id:skn_0410:20170210020057p:plain

 

Now we have new field "Table Name".

f:id:skn_0410:20170210020213p:plain

Then we can use "Table Name" as additional dimensions which make us easy to control both header and measures.

Let me explain.

The initial difficulty of water fall chart was how to conbine "Last Year" and "This Year" as bucket sum, and "Delta" as break-down to Province.

 

With having additional dimension, we can control this.

 

[Header 2]

if [Table Name]="Clipboard_20170209T095719.txt" then "Last Year"
elseif [Table Name]="Clipboard_20170209T095719.txt1" then "This Year"
else [Province] end

 

Talking about measusres, one important consideration is needed with this approach.

Because we tripled all the ata with Union, to get corect numbers, we always take care to avoid data duplication.

In case of LOD, we need to specify the [Table Name].

 

[Sales Last year]
{fixed [Table Name]:sum(if [Header Time Range] ="Last Year 3 months" then [Sales] else 0 end)}

 

[Sales This year]
{fixed[Table Name]:sum(if [Header Time Range] ="Curent 3 months" then [Sales] else 0 end)}

 

[Sales Delta]
sum(if [Header Time Range] ="Curent 3 months" then [Sales] else 0 end)

-
sum(if [Header Time Range] ="Last Year 3 months" then [Sales] else 0 end)

 

// We aggregates the measure and need to add "attr" on [Table Name]

 [Delta Height]

 if attr([Table Name])="Clipboard_20170209T095719.txt"
then min([Sales Last year])
elseif attr([Table Name])="Clipboard_20170209T095719.txt1"
then min([Sales This year])
else [Sales Delta] end

 

 You can get this height of Gantt.

f:id:skn_0410:20170210023914p:plain

Because the delta should be cumulated and sorted, add table calculation of "running_sum" and sort acsending.

f:id:skn_0410:20170210024108p:plain

 

f:id:skn_0410:20170210024251p:plain

 

f:id:skn_0410:20170210024353p:plain

 

You can see the height with "running_sum" does not work on "This year", because this year is already the result of "Last year" + "Delta".

 

f:id:skn_0410:20170210024455p:plain

 

So I need to chage the formula little bit.

 

[Delta Height 1]

if attr([Table Name])="Clipboard_20170209T095719.txt"
then min([Sales Last year])

elseif attr([Table Name])="Clipboard_20170209T095719.txt1"
then 0
else [Sales Delta] end

 

f:id:skn_0410:20170210025003p:plain

 

f:id:skn_0410:20170210025053p:plain

Height is done!!

Then try put "Size".

Again, the sie should be negative becasue that color need to connect the gap between the current height and previous( next left ) height.

Plus, for "Last Year" and "This Year", whole the "Height" should be the "Size" to fill to the bottom of the chart.

[Delta Size]

if attr([Table Name])="Clipboard_20170209T095719.txt"
then - min([Sales Last year])
elseif attr([Table Name])="Clipboard_20170209T095719.txt1"
then - min([Sales This year])
else -([Sales Delta])
end

 

 

f:id:skn_0410:20170210025520p:plain

We got it!!

 

Then Text.

Because the "Size" is negative, to put text label we need to add field for text.

Simply

[Delta Text]

- [Delta Size]

 

Then put color.

When we put [Delta Text] to color, the delta color is not recognizable becasue the scale is too much different from "Last Year" and "This Year".

f:id:skn_0410:20170210025940p:plain

 

So we also need to create another calculated field to show "Color".

 

[Delta Color]
if attr([Table Name])="Clipboard_20170209T095719.txt2" then [Delta Text]
else 0 end

 

We can force the "Last Year" and "This Year" value as 0 only show the value on "Delta", by specifying the [Table Name].

 

f:id:skn_0410:20170210030319p:plain

 

Much better.

Then the merit of this approach is very flexible Y-axis modification.

 

For example, when we'd like to highlight the "Delta" scale.

I know not starting from "Zero" on Bar graph is sometimes not good way, though,

I understand the request is also reasonable.

 

In case of Gannt Chart, "excluding Zero" does not change the Y-axis range.

f:id:skn_0410:20170210032352p:plain

 

f:id:skn_0410:20170210032436p:plain

Because the size of both left/right end reaches to the bottom.

Anyways,we want to specify the minimum line.

We want to use 90% o the minium value with round.

[Minimum]

round(window_min(running_sum([Delta Height 1]))*0.9,-4)

 

Then we can shorten the size of Gantt with this line.

 

[Delta Size 2]

if attr([Table Name])="Clipboard_20170209T095719.txt"
then - (min([Sales Last year])-[Minimum])
elseif attr([Table Name])="Clipboard_20170209T095719.txt1"
then - (min([Sales This year])-[Minimum])
else -([Sales Delta])
end

 

And add [Minimum] to reference line.

f:id:skn_0410:20170210032902p:plain

And change the format a little bit.

 

f:id:skn_0410:20170210032957p:plain

Only Showing Greater Than <Value>

 

f:id:skn_0410:20170210033138p:plain

 

Hide Header 

f:id:skn_0410:20170210033620p:plain

 

And finally got the chart like this.

f:id:skn_0410:20170210033719p:plain

Tableau Public

 

This becomes another long post....

Anyways,  

Enjoy Tableau !!

 

 

 

 

 

 

 

 

 

 

 

 

Example of Water Fall Charts -1

Temprture is getting wamer and spring is aruond the corner!

 

I got couple of questions regarding Water Fall Chart.

So I post the one exapmle here.

Of course there are multiple methods to create the charts and this is not always the simplest way.

 

Goal is....

Compare This year's "The latest full three month" and "Last year's same period"

For example, today is Feb 8th , so we need to compare 2016/11~2017/02 with 2015/11~2016/02.

And we also need to show Delta break down.

The Final Chart image is like below.

f:id:skn_0410:20170209085406p:plain

Starting from Calculated Field.

[Header Time Range]

if [Custom Order Date] <= datetrunc('month',today())-1
and
[Custom Order Date] >=dateadd('month',-3,(datetrunc('month',today())))
then "Curent 3 months"
elseif [Custom Order Date] <= dateadd('month',-12,datetrunc('month',today())-1))
and
[Custom Order Date] >=dateadd('month',-15,(datetrunc('month',today())) )
then "Last Year 3 months"
end

 

Datetrunc brings the first date of month and "datetrunc('month',[date])-1 " brings the last date of last month. 

"dateadd('month',[date])" is shifting the date by month.

One note here is that to shift one year, we'd better use "12 months" than "1 year" to avoid an error on "2/29 of Leap Year".

It correctly select the range.

f:id:skn_0410:20170209090308p:plain

f:id:skn_0410:20170209090340p:plain

We will create three worksheets and combine on Dashboard.

1. Current year Worksheet (Bar Chart)

Put "Sales" on Rows and Put "Header Tiemn Range" on Filter.

Then select "Curent 3 months" on filter.

f:id:skn_0410:20170209093226p:plain

 

2. Last year Worksheet (Bar Chart)

Put "Sales" on Rows and Put "Header Tiemn Range" on Filter.

Then select "Last Year 3 months" on filter.

 

3. Create Delta Worksheet (Gantt Chart)

First, calculate the delta between this year and lat year.

[Sales Delta]

sum(if [Header Time Range] ="Curent 3 months" then [Sales] else 0 end)
-
sum(if [Header Time Range] ="Last Year 3 months" then [Sales] else 0 end)

 

Gantt is consist of "Heighet" and "Size".

Height:

[Sales Last year]

{fixed:sum(if [Header Time Range] ="Last Year 3 months" then [Sales] else 0 end)}

 

[Delta Height]

attr([Sales Last year]) + running_sum([Sales Delta])

 

Adding each "Provinve" (in this case) delta on Last year's sales.

 Using LOD brings Last year's Sales and using running_sum brings sequential height for respective "Province".   

 

[Delta Size]

-[Sales Delta]

 Size should be "Negative" in this case, because the hight is "Last year" + "Delta".

And the colored part should bridge between Current height and previous height. (Next left)

 

f:id:skn_0410:20170209094619p:plain

Sort Province with "Delta" Ascending.

The reason to use "Ascending", not "Descending" is to keep the total height of Graph shorter to show maximum Range. 

If we take opposite way, the "Hill" is made and grapgh range(height) becomes unneccessarily tall. 

 

f:id:skn_0410:20170209094707p:plain

Now let's try to put three worsheets on dashboard.

 

f:id:skn_0410:20170209095241p:plain

We see couple of problems.

1. Height is not aligned beteween Current and other tow.

2. Y-axis Zero line is not alighned between Delta and other two. 

f:id:skn_0410:20170209095751p:plain

Then, to align height, we use "Reference Line"

In all the case, we need to pick "highest point across three worksheets".

Because we sorted Delta as acsending, the highest point is max of last year or this year.

[Sales Last year]  // already exist

{fixed:sum(if [Header Time Range] ="Last Year 3 months" then [Sales] else 0 end)}

 

[Sales This year]

{fixed:sum(if [Header Time Range] ="Curent 3 months" then [Sales] else 0 end)}

 

[Max Height for Ref Line]

max([Sales Last year],[Sales This year])

 

Put [Max Height for Ref Line] to Detail shelf and set "Reference Line".

f:id:skn_0410:20170209100407p:plain

 

f:id:skn_0410:20170209100928p:plain

 

f:id:skn_0410:20170209101042p:plain

 

Same thing for other two sheets, and let's see dashboard.

 

f:id:skn_0410:20170209101259p:plain

It's still miss-alighed.

Because of "Text" on top of the bar chart.

We still want to keep the text, so change the positoin.

f:id:skn_0410:20170209101449p:plain

Let's see the dashboard again.

Now last year and Current year is aligned, but Delta still little bit off.

f:id:skn_0410:20170209101609p:plain

We need to align Zero Line to fix this.

We can add "Blank" box on Dashboard, but I'm afraid it changes the size according to the screen size.

To algen Zero line, we need to add dummy header.

Let's fix Wrap or Not because it changes the Zero line position under "Automatic setting"

f:id:skn_0410:20170209101947p:plain

 

 

f:id:skn_0410:20170209102113p:plain

 

Then we add Header to Last year and Current year worksheets.

To align the height of header, we want to pick longest Province name

[Header Year]

{fixed:max(if len([Province])={fixed:max(len([Province]))} then [Province] end)}

 

Then put this field to both sheets.

 

f:id:skn_0410:20170209102504p:plain

 

Then Change the Font color of Dummy Header to white.

f:id:skn_0410:20170209102922p:plain

 

 

f:id:skn_0410:20170209102959p:plain

f:id:skn_0410:20170209103205p:plain

You can add "text" tile with floating.

(This is off-position little bit when the screen size changes)

f:id:skn_0410:20170209103357p:plain

f:id:skn_0410:20170209103553p:plain

 

f:id:skn_0410:20170209103518p:plain

 

f:id:skn_0410:20170209103657p:plain

f:id:skn_0410:20170209103734p:plain

 

Same thing for Current Year.

 

Then Hide Header from both Delta and Currenmt Year.

f:id:skn_0410:20170209103920p:plain

 

Finally you got completed water fall chart.

f:id:skn_0410:20170209104030p:plain

Tableau Public

 

Enjoy Tableau!

God is in the details - Change the Header Position to TOP-

God is in the details-

I'm not sure this idiom is undertandable in English or not, anyways, what I'd like to say is if the detail is good entire sysytem should be good.

Of course this kind of idiom is not alwasy true, but I think this matches the case I explain today.

Here is a simple chart.

f:id:skn_0410:20160819064221p:plain

Now add Cutomer Category on Column.

f:id:skn_0410:20160819064616p:plain

Becasue we have multiple Bar charts on columns, we want to move the [Cutomer Segment] Header to the Top to narrow the distance from Top chart to the header.

f:id:skn_0410:20160819064840p:plain

 

Let's try to add same pill of [Custoemr Segment] on Columns.

f:id:skn_0410:20160819065518p:plain

The results looks like below.

f:id:skn_0410:20160819065624p:plain

We got headers on both top and bottom.

This is good, but little bit noisy.

So, we want to remove the header from bottom.

 

Let's try to hide header of the bottom.

f:id:skn_0410:20160819065815p:plain

End in this results.

Both headers are gone unexpectedly....

This does not work.

f:id:skn_0410:20160819065856p:plain

.

Ay other idea?

 

Yes, we can try to add other field.

Which field is good?   We have a clear condition requirement.

If the field has multiple values, it impact to the view itself. Then the field should have only one value.

Simply saying, we'd better add one dummy field, like this.

[Dummy_Header]

"A"

It's better to put string than numeric value because handling numeric value is more troublesome that string value.

Let's put this [Dummy_Header] into the columns at right side of [Custoemr Segment].

f:id:skn_0410:20160819071032p:plain

Looks good. Now uncheck "show header" of [Dummy_Heaser]

f:id:skn_0410:20160819072618p:plain

 

f:id:skn_0410:20160819071204p:plain

Yes, this is what we wanted.

 

This is good opportunity, so let's try some extreme cases.

f:id:skn_0410:20160819071811p:plain

 

f:id:skn_0410:20160819072024p:plain

You can see the right-end field's value is set as the bottom Header, and rest of fields' value are set as top headers with left side field as higher positioin.

 

Very samll TIPs, but better to have!  

Enjoy Tableau!

 

Tableau Public

 

Common misconception - Cannot Draw Stacked Line Chart? -

This is a kind of common misconception accordin to my experience.

That is "We cannot draw stacked line chart."

I heard this comment from multiple people, even I did think so until recently.

The reason is, when I create simple charrt with dragging below three pills on the each field, Tableau automatically draws a chart as below.

f:id:skn_0410:20160817223720p:plain

 

One confusion at very begining as Tableau newby, is difference between Line Chart and Bar/Area Chart.

When we change the cahrt type to "Bar", the graph cahnged as below.

f:id:skn_0410:20160817223055p:plain

It's stacked.  

Then change the type to "Area", it's also stacked.

f:id:skn_0410:20160817223216p:plain

From this behavior, I missunderstood that Tableau cannot draw stacked line chart.

But that's not true.

 

Again, there is check/uncheck problem.

Go back to line chart, and go "Analysis" from menu bar.

f:id:skn_0410:20160817225114p:plain

 

Then you can get stacked AREA Chart !!??

f:id:skn_0410:20160817225251p:plain

This was a second reason I thought that Tableau cannot draw Stached Line Chart.

But we can see the chart type shows "Automatic".

Now we change the type to "Line" again.

Finally we got stacked Line Chart.

f:id:skn_0410:20160817225508p:plain

 

On the other hand, when we change the chart type to "Bar" and stack mark to "Off"

f:id:skn_0410:20160817225702p:plain 

we got non-stacked Bar Chart.

f:id:skn_0410:20160817225824p:plain

 In this view, the color is overwrapped and does not make sense as a chart.

So, change the color transparency, 

f:id:skn_0410:20160817230056p:plain

and got below view.

f:id:skn_0410:20160817230151p:plain

Also on this non-stacked bar chart, with changing the order of category in color shelf, we can change the order of layer of color.

f:id:skn_0410:20160817230849p:plain

Like below.

f:id:skn_0410:20160817230919p:plain

 

Enjoy Tableau !!

public.tableau.com

It's equipped with One Check/Uncheck - Show Blank Cells in the Table

Sometimes easy view takes 100 of steps to be created.

exceltableau.hatenablog.com

But somtimes it's done only by one Check/Uncheck.

exceltableau.hatenablog.com

 

I got a request that he wants to show the "Blank" Cells in the table.  

He wanted to show the table like below.

f:id:skn_0410:20160817052608p:plain

This looks good, but when we filtered 4th Item of " 6" Cubicle Wall ***"  as an example, the table becomes like below.

f:id:skn_0410:20160817052744p:plain

It seems good at a glance, but you can see it excludes Null Value and doesn't show Yr2010 data in the column.

So, the question was how to show this colum in the table.

f:id:skn_0410:20160817052947p:plain

 

I tried to use the formula which can put "0" in the table.

[Sales (ZN,lookup)]

zn(lookup(sum([Sales]),0))

 

f:id:skn_0410:20160817053158p:plain

But when I filter this item only, the view still hides Yr 2010.

f:id:skn_0410:20160817053415p:plain

 

The answer was quite easy AS LONG AS you know it.

Check "Show Empty Columns"

f:id:skn_0410:20160817053636p:plain

 

It still does not show the missing column like this.

f:id:skn_0410:20160817053822p:plain

That's the reason I was confused, but one more check, which is "show missing value".

f:id:skn_0410:20160817053936p:plain

 

If the column is the dimensioin which is not "Date" related, without checking "show missing value", you can display blank cells like this.

f:id:skn_0410:20160817054210p:plain

One thing we need to pay attention is "Filtering"

f:id:skn_0410:20160817054600p:plain

At this point, we only filter "Product Name".

But when you click the triangle of Column of "Product Container", (right corner of the pill)

and select Filter, 

f:id:skn_0410:20160817054804p:plain

It brings below window.

f:id:skn_0410:20160817054835p:plain

When you click OK, the table changed as below unexpectedly.

f:id:skn_0410:20160817054937p:plain

We can remove the filter to show all the items or we can select items which we want to show just as usual filtering fuctions.

f:id:skn_0410:20160817055710p:plain

 

There are still many things we need to learn.

Enjoy Tableau !!

 

public.tableau.com

 

Such a simple view, it required 100 steps!! - Having Multiple KPIs -

Olympic is ongoing and this is the typical timing when people in U.S. think about their nationality.

It's natural for two BIG countries to compete the number of Medals, but I'm afraid that they compete each other too much seriously in every arena.....

 

Today's topic is one of most famous FAQ.

That is

  • "How to Put multiple KPI in the table"  or
  • "How to Put different color to multiple Measures"

The Final VIZ I want to create is like below.

f:id:skn_0410:20160810025841p:plain

Three different measure (Sales, Profit, and G.M.%) must have different color.

It's easy to put one measure in the table and color them by only one KPI.

For example, putting Sales data and Sales KPI as color, we can get a table like this. 

f:id:skn_0410:20160810030446p:plain

Also it's easy to put three different measures in Column without color KPI.

f:id:skn_0410:20160810030746p:plain

 

But when you try to put colors on these three measures, we will see all three measures show same color.  

f:id:skn_0410:20160810031001p:plain

 In other words, Profit and G.M.% are also showing "Sales KPI color".  Defenitely, this is not we want.

Again this is one of the most frequently asked questions and this is fairly common view.

And we have the answer.

However, this is not a kind of "Here is an answer !" type of situation, but level of "This will work if you really need something..." 

If Tableau want to dominate the position of top BI tool, this is one of the most important function I strongly recommend to add. If Tableau development team think that the problem is already solved with current shared approach, that is not good understandings.

Imagine how difficult it is that community members spend long time to answer this question every day.....

Anyways....

 

 How to approach this problem?

 

We need "Place Holder" to put fields in, which is "1".

You can add "Dummy" as calculated field with numeric 1. 

f:id:skn_0410:20160810033924p:plain

Then put this fields in column with "Minimum" as Measure.

f:id:skn_0410:20160810034352p:plain

Then copy twice for three measures.

Now we have three Bar Graphs.

On first Bar Graph put "Sales" filed on Label and "KPI (Sales)" on Color.

f:id:skn_0410:20160810034656p:plain

Then edit Axis,

f:id:skn_0410:20160810034857p:plain

f:id:skn_0410:20160810035118p:plain

 

f:id:skn_0410:20160810035204p:plain

 

To add Title on the Top instead of Bottom, same field of "Dummy" needs to be put to the secondary Axis.

 

f:id:skn_0410:20160810035411p:plain

 

f:id:skn_0410:20160810035757p:plain

 Tableau's "Automatic" chart type assignment sometimes do rediculous job, but don't worry.

Change original Graph type to Bar Chart, secondary Chart tyep to Text.

Actually , we don't care what is the secondary graph type because this is only for the purpose of putting "Header" on the Top Axis.

f:id:skn_0410:20160810040014p:plain

Edit Axis on the secondary (Top) axix.

f:id:skn_0410:20160810040147p:plain

Type in "Title"

f:id:skn_0410:20160810040337p:plain

Remove Tick Marks

f:id:skn_0410:20160810040412p:plain

Then Change the Color Transparency to "Zero" 

 

f:id:skn_0410:20160810040544p:plain

 

Then Go to Bar Chart and Change the Alignment Horizontal to "Middle".

f:id:skn_0410:20160810040845p:plain

We finally got this view.

f:id:skn_0410:20160810040928p:plain

 

And same steps need to be done on two other Measures of "Profit" and "G.M.%".

Now we got this view.

f:id:skn_0410:20160810041425p:plain

 To fill the Gap of vertical "blank", we change the Size of Bar Chart.

f:id:skn_0410:20160810041836p:plain

After we do x3 of above, we got this View as Final x Final

 

f:id:skn_0410:20160810041952p:plain

 Such a simple view, it required more than 70 steps to accomplish without any failure !! 

Considering some trial end erros (Erros require 2x steps),
it requirs 100 steps!!!

I don't have any simpler way to create this type of view.. Definitely Tableau Development Team's awareness and support is neeed.

Tableau community has the feature of collecting "Ideas" and I think below idea is close to our needs. You can vote here of course.

Conditional Formatting of Measure Names/Measure... |Tableau Support Community

Though, one of difficulties, I think, is the way "Naming"/"Expressing" this requirement itself is not consistent...

Anyways, Enjoy Tableau !!

public.tableau.com

 

 

 

Just One Check/Uncheck Changes it - Dashboard Actions -

Yeah!  Ichiro hit 3000th hit in MLB !!!

Ichiro Suzuki - Wikipedia, the free encyclopedia

But actually nobody around me mentioned that.  

In U.S. people does not pay attention to baseball unless they have stadium in thier city.

That's different in Japan.   Everybody loves Ichiro and all the newspaper mentioned that.

I love him from his first year in Japan Professional League.

 

By the way, 

I got a question about unexpected Dashboard Actions behavior .

His dashboard was like this

f:id:skn_0410:20160809072522p:plain

Three worksheet were consolidated in a dashboard.

Actually this KPI is very good from the perspective of having multiple KPIs in one table.

Somtimes to have multiple KPI in one view is littel bit troublesome. (I can post this topic later) 

But in his sheet, that problem is solved by using "Countinous" value as KPI.

If this is Discrete, we can not assign multiple colors in respective KPIs.

His dashboard action as dedigned as Sheet "KPI" as filter.

and "Deital" and "Sales by Product" shows only filtered value.

 

It woked well like below.

f:id:skn_0410:20160809073006p:plain

 

f:id:skn_0410:20160809073051p:plain

 

The problem was that

When user click "Customer Name" which makes more sense considering the  relationships,

f:id:skn_0410:20160809073211p:plain

Filter did not work....

It was strange for me because my sample did work with exact same design.

 

This was caused by "AGAIN", just one check/uncheck type of thinig.

 

The difference was below

f:id:skn_0410:20160809073648p:plain

 

When you uncheck "Run on single select only", you will allow users to select below type of selections.

f:id:skn_0410:20160809074616p:plain

You can select multiple KPIs with CTRL key.

This is really useful in some case, but not alwasy good when you consider the Dashboad View which requires big scroll.

 

So you can check this box of "Run on single select only" when you want to limit one cutomer at same time, BUT, to allow "Costomer Name" as filter, you MUST uncheck this box.

I have not relized "Select Customer" is sistematically recognized as "Multiple Slection".

 

Small thing and pretty understandable, but we spent couple of hours to understand the Dashboard behavior as a fact.

 

Learning, Learning, Learning...

public.tableau.com

 

Enjoy Tableau !!