ウォーターフォールチャートの一例 - ２ - 目指せ！世界でいちばんのタブロー使い

前回の投稿の別アプローチです。

こっちの方が簡単だとは思いますが、環境面の制約があります。

こちらのアプローチだと、ダッシュボードが不要で、ひとつのワークシート上にグラフを作成できます。

このメリットは非常に大きいです。

しかしながら、この方法は、データを複製するのに Unionを使用するため、以下のような制約があります。

Unionはバージョン9.3かそれより新しいバージョンでしか使えません。
いくつかのデータ接続タイプでは、Unionが使えません。
Unionを使うことにより、この場合データ構造の複雑さが増すので、反応速度に影響が出る可能性があります。

上記の制約に引っかからないなら、こちらのアプローチでより手軽にウォーターフォールチャートを作成できます。

では、Unionから始めましょう。

新たに、「Table Name」というフィールドが追加されました。

このフィールドは、追加のディメンションとして使用でき、これを用いてヘッダーや、メジャーを新たに作成することで、いろんな応用ができます。

ウォーターフォールチャートの難しい点は、「昨年度」「今年度」は合計で表示し、

「差分」に関してだけ、「Province」のディメンションを追加することです。

Unionで新たに作成された「Table Name」を使用して、この点を解決していきます。

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

Unionを用いたときの注意点ですが、データが3重になっているので、普通にメジャーを合計すると、元の数字の3倍になってしまいます。ですので、重複を避けるため、「Table Name」をうまく使って、メジャーフィールドの計算式を構築する必要があります。

以下のLODの例の場合、「Table Name」をFix しておかないと、3倍の数字が出てくることになります。

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

// データは、個別のデータでなく、集計されたものとなるので、 "attr" を [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

これらの計算フィールド配置して、以下のようなガントチャートが作成できます。

まずは、シート名の順番に並んでいるペインを「差分」が真ん中に来るように、ダウッラッグ＆ドロップで移動します。

次に、数字は累計にした上で、「差分」の小さい方からソートしてあげる必要があります。

そのまま累計にすると、「今年分」のプロット位置が、異常に高いところになります。

これは、「今年分」は、「昨年分」+「差分」なので、さらに「今年分」を累計に足すと、ダブルカウントしてしまうからです。

計算式を少し変更します。

[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

これで、「高さ」の位置は合いました。

次は、ガントチャートのもうひとつの要素、「サイズ」を考えます。

「サイズ」は、負の数で考える必要があります。なぜなら、ひとつ前の数字とのギャップを埋めたいので、今ある「高さ」の位置からは戻ることになるからです。

さらに、「昨年分」と「今年分」は、高さが丸ごと「サイズ」ということにして、グラフの底まで色づけしてあげないといけません。

[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

できました～。

次に、ラベルです。

[Delta Text]

- [Delta Size]

サイズを負の方向にしたので、ラベルに戻すのに、マイナスをかけます。

同じフィールドを「色」に持っていってみましょう。

「昨年分」「今年分」と「差分」のスケールが違いすぎて、「差分」の色の違いがわかりません。

そこで、色を示すために別のフィールドを作成します。

ここでも、「Table Name」を使います。

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

「昨年分」「今年分」を「0」に固定して対応しました。

だいぶマシになりました。

このアプローチの大きなアドバンテージのひとつは、Y 軸の高さが非常にフレキシブルに調整できることです。

ここでは、「差分」の「Province」ごとの違いを明確にするため、Y軸のスタートをゼロより大きい値にしてみます。

通常、棒グラフでは、誤解を避けるためゼロ点を省くことはお勧めできませんが、この場合は、誤解を招く恐れも少ないので、「ゼロ点を除く」を設定します。

何も起こりません。

このガントチャートの場合、「昨年分」「今年分」の「サイズ」要素がグラフの底まで達しているため、特別に設定してあげない限り、ゼロが最小値になっています。

そこで、まずは、最小の高さをどこにするか適当に決めます。

出てくる最小値の90%にして、適当に四捨五入してみます。

[Minimum]

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

そして、「サイズ」要素そのものを、決定した最小値までだけ届くように変更します。

[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

そして、最小値をレファレンスラインに挿入します。