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!