Subscribed unsubscribe Subscribe Subscribe

ウォーターフォールチャートの一例 - 1

しばらくQ&Aに注力していたので、全然更新してませんでした。

ウォーターフォールチャートに関して、ちょっと解説を依頼されたので、ここにでも書いてみようかと思います。

最終形はこのようなものを想定しています。

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209085406.png

 

まずは、ゴールの確認。依頼の内容に合わせるため、期間設定に関してちょっと特殊要件が入っています。

直前の3ヶ月(今月は含まない)と同期間の昨年分を比較し、差に関してブレイクダウンして表示する。というものです。

今日は2/8なので、「今年分=2016/11/1~2017/1/31」、

「 昨年分=2015/11/1~2016/1/31」 となります。

まずは、計算式

 

[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 は 各月の「ついたち」を返してきます。ですので、 "datetrunc('month',[date])-1 " は、先月の最終日を返してきます。
"dateadd('month',#,[date])" は、# ヶ月分、日付をずらします。

ひとつの注意事項は、1年ずらす時、"1 year"  を使用するのではなく、"12 months" を使用したほうがよいという点です。理由は、「年」を使用すると、うるう年の時に、2/29 の1年前が3/1 になってしまうためです。

以下のとおり、正確に分類できていました。

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209090308.png

 

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209090340.png

 

 

ここからは、グラフを作成するのですが、基本コンセプトは、3つのワークシートを作成し、ダッシュボード上で結合するというものです。

このやり方は、ひとつひとつのグラフは簡単に作成できるのですが、範囲とかヘッダーのあわせ込みが非常に厄介です。そのあわせ込みに関してがノウハウになります。

まず、今年度のシート。

 "Sales" を 行に持っていき、"Header Tiemn Range" をフィルタに設置します。フィルタで  "Curent 3 months" を選択すればこのシートはいったん OKです。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209093226.png

 

 

 

次に昨年度のシート

同様に、"Sales" を行に、"Header Tiemn Range" をフィルタに設置し、"Last Year 3 months" をフィルタから選択します。

 

さて、厄介な「差分」シートです。

っこでは、ガントチャートを作成します。

ガントは、「高さ」と「サイズ」から成り立ちます。

「高さ」のポイントから「サイズ」分に色づけするイメージです。

 

まずは、いくつか計算式のフィールドを準備します。

 

[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)

 

[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])

 

各 "Provinve" (in this case) の差を 昨年のグラフに累積で加算していきます。

LODを使って昨年分のグラフの値を固定し、running_sum で累計を算出します。

 

次に「サイズ」です。

[Delta Size]
-[Sales Delta]

サイズは、マイナスで扱ってあげる必要があります。なぜなら、「高さ」を累計にしているため、ひとつ前の「高さ」とのギャップを示すためには、「差分」の量だけ、戻ってあげる必要があるためです。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209094619.png

 

 

"Province" を昇順にソートします。昇順の理由は、累積降下分を先に持ってきて、お碗のようにすることにより、「差分」のガントチャートの高さが、余計なスペースを使うことを避けるためです。逆だと、アーチ上になって、最大値が大きくなってしまいます。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209094707.png

 

いよいよ、ダッシュボード上で結合します。

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209095241.png

 

最初に述べたとおり、いくつか問題があります。

高さが合っていない事と、Y軸のゼロ点がずれていることです。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209095751.png

 

 

まず、高さの調整ですが、「Reference Line」を使います。

すべての場合において、高さを合わせる=高い方に揃える ことになります。

既に、「差分」グラフをお碗型にしたので、最大値は

「昨年」「今年」の両グラフの高いほうになります。

 

[Sales Last year] //  既に作成したフィールド
{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])

 

 

[Max Height for Ref Line] をリファレンスラインに追加します。

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209100407.png

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209100928.png

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209101042.png

 

同様のことを、「昨年」「差分」のグラフでも行います。

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209101259.png

 

まだ、ずれています。

これは、数値をあらわすテキストが、上部に表示されているため、自動調整でずれが生じているためです。テキストは必要だとして、表示位置を変更します。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209101449.png

 

 

ようやく、「昨年」と「今年」のグラフの高さは合いました。

「差分」グラフとは、ゼロ点を合わせ込んでいないので、まだ合っていません。

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209101609.png

 

”BLANK” のタイルを使って、ゼロ点を合わせる方法もあるのですが、ディスプレイのサイズが変わったときにずれる可能性が高いので、もう少し手の込んだ方法を使います。

 

まずは、Provinceの表示を固定するため、「差分」のFormatを変更します。

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209101947.png

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209102113.png

 

 

次に、「昨年」「今年」のグラフにヘッダーを追加するため、一工夫します。

「差分」に表示されている Province の中から、最も長い名前をピックアップします。

 

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

 

これを、それぞれのシートのヘッダーに追加します。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209102504.png

 

 

さらに、追加したヘッダー名のフォント色を「白」にします。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209102922.png

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209102959.png

 

 

で、↓こうなります。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209103205.png

 

さらに、その上からText オブジェクトを追加します。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209103357.png

 

 

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209103553.png

 

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209103518.png

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209103657.png

 

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209103734.png

 

 

同様のことを、「今年」のシートでも行います。

その後、ヘッダを非表示にします。

 

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209103920.png

 

いちおう、これで完成です。

https://cdn-ak.f.st-hatena.com/images/fotolife/s/skn_0410/20170209/20170209104030.png

 

長かったですね。。

 

別のやり方を、後日投稿します。

もうちょっと簡単かなとは思いますが、書いてみないとわからないですね。

 

では、Enjoy Tableau!