月次の差額分析、予実対比がしたい（Tableau）
DATA Saber BridgeのPublic課題で作成したVizはこれまで子ども関係のネタばかりでしたが、今回は仕事関係のネタにしてみました。
現在の業務では事業計画の進捗管理、コスト管理を担当しています。月次で予実の差額分析や事業計画の策定を支援するのがメインです。
Tableauを使って月次分析を効率よく、もっとわかりやすくできないか取り組んできましたが、DATA Saberプログラムで学ぶ中で改善のアイデアを色々得ることができたので記事にまとめたいと思います。
（より詳しい操作は文中でリンクしているKT動画を参照ください）
Excelの予実データ、Tableauにどう取り込むか。
Tableauのサンプルスーパーストアのデータは分析しやすいように項目が整理されていますが、現実の業務ではそんなExcelのデータはめったにありません。人が把握しやすいように、数値を入れやすいようになっています。
予実分析をする場合のデータも基本的には月のデータが横持ちで、予算と実績は別ファイルか別シートになっているケースが多いと思います。
DATA SaberプログラムのOrdにも予算と実績対比の問題（Ord3のQ11）がありますが、この問題で使うサンプル - スーパーストア - 予算.xlsxのファイルが実際の業務で使う様式に近いのではないでしょうか。
設問では注文データを集計した結果と予実対比していますが、今回はわかりやすいようにサンプルスーパーストアのデータを少し加工して、予算と実績が１つのシートに入ったものを作成しました。
区分の列に予算、実績と入っていますが、実務だと「当初計画、修正計画、実績」のように複数あったり、さらに年度を追加して各年度毎に計画と実績のデータがあると思います。その場合でも基本的には何のデータかわかる区分があればOKです。
これを読み込み、データソースの編集で1～12月を選択して、各月をピボットします。
ちなみに、シートが予算と実績で分かれている。こんな場合は、データソースの編集でシートをユニオンすれば一つのデータにまとめられます。
データを縦持ちにして、ピボットのフィールド名を日付に変更して名前を「年月」に。ピボットのフィールド値の名前を「金額」に変更して準備完了です。
これで月単位、年単位で予算、実績ごとに推移を確認できるようになりました。読み込んだデータだけで予実と差額を表現しようとすると、こんな感じの棒グラフになります。
どの地域が差額が大きいか確認する等、自分で分析するだけならこれでもなんとかなるかもしれませんが、はっきり言ってわかりにくいです。
共有しても？？？だと思います。
予実の分析 ～ブレットグラフ～
予算と実績の管理といえば、ブレットグラフ。ということで、Ord6のQ3や上図のように予実で二本の棒グラフを並べるのではなく、目標に対する達成状況を確認できるようにしてみます。
このデータの場合だとメジャーが「金額」の一つしかないので、比較できるように、「区分」を使って「予算額」「実績額」として別々のメジャーになるように計算フィールドを作成します。
１年分の全データを「地域」ごとに「予算額」「実績額」で比較するということで、この３つを選択して「表示形式」からブレットグラフを選ぶと、予算に対する達成状況が表現できます。
あとはOrd6のQ3と同様、達成しているかどうかで色分けをするとより分かりやすくなります。でも、このグラフ、自社では不評でした。
達成状況じゃなくて「差」がどうなっているのか知りたい。未達成があるのはわかるが、これだとパッと見てどのくらいのインパクトがあるかわからないというのが意見でした。
差額の把握 ～ウォーターフォール チャート～
経営会議資料や親会社も含めた事業計画の報告資料を確認すると確かにブレットグラフの表現はなく、差の把握で良く使われているのはウォーターフォール チャートでした。
ということで、このデータを使ってどこの地域でどの程度、差が発生しているのかわかるようなチャートを作ってみます。参考にしたのはOrd9のQ2です。
まず、差額を把握したいので、「差額」を計算フィールドで作成します。
また作るの？と言われそうですが、作ります。
チャート作成に必要なフィールドがなければ作ってしまう。これがTableauの便利なところなのですが、最初はどう作ればいいかわからないので結構苦労しました。今回DATA Saberの技術課題に挑戦していく中でだいぶ鍛えられました。
「差額」は実績ー予算で表現したいので、計算フィールドで「区分」をもとに「金額」のデータを設定します。
この「差額」を「地域」ごとの累計にして、マークをガントチャートに変更。さらに下を塗りつぶすために、サイズにSUM(-[差額])を入れるとをウォーターフォール チャートの完成です。
このままだと差額自体の±がわかりにくいので、SUM([差額])を色に入れて２ステップのステップドカラーにすると、どの地域がどの程度、目標の数値に対して増減したのかがわかりやすくなります。
地域だけじゃわからない。カテゴリは地域差あるの？と更問がきても大丈夫。「カテゴリ」を列にドロップすれば確認できます。
更問対策 ～差額理由の表示～
差額の凸凹はわかった。じゃあその原因は何？これから改善できるの？次に出てくる質問はこの辺りです。
実務では現場へのヒアリングも同じ課題感で確認していきますが、集計結果を見る人によって着目するポイント（この場合カテゴリや地域）が違うということは良くあります。
トップNだけ報告しても実は違うところが知りたい。そうなると、どこからともなく更問がまた現場に落ちてくる。なんてことも。
だったらその報告内容もダッシュボードに表示してしまえ～ということで、Excelで毎月差額理由を報告しているという想定でデータを作成し、一覧表示するようにしました。
ダッシュボードに配置する際にブレットグラフやウォーターフォール チャートをクリックすると地域でフィルタがかかるようにすれば、「その原因は？」の答えをその場で確認できます。
報告対象月かどうかは年月とパラメータで設定した報告対象月を比較して判定する計算フィールドを作成します。報告月の場合は2、過去月の場合は1、未来月の場合は0のフラグを設定します。（下図の［報告フラグ］）
一覧を表示するために使ったのがLOD計算です。報告フラグが報告月のものだけ「差額理由」を表示することはできますが、予算、実績、差額はそのままメジャーバリューに入れても報告月（単月）の合計値になってしまいます。
予実と差額の数字は報告対象月までの累計額で表示したい。ということで、報告単位とした地域×カテゴリを使って、それぞれの累計額を出す計算フィールドを作成します。
参考にしたのは、難問とのコメントが多い、Ord8のQ8のFIXDを用いた計算です。Ordの回答ではオーダー日毎にソート用のカテゴリと一致する場合のみ売上を計算するという計算式になっています。
LOD計算の集計式では条件式も使えることをこの設問で初めて知りました！
報告内容一覧でも同じように、報告月までの差額を集計するように設定します。
差額の累計以外にも予算額（累）、実績額（累）も同様に計算フィールドを作成します。
なぜ、報告フラグで判定しているかというと、予算は一年分のデータがあるので、
{ FIXED [カテゴリ],[地域] : sum( [差額] )}
とすると、報告月が年度途中の場合でもデータがある一年分が集計されてしまうからです。
さらにOrd8のQ8と同じならEXCLUDEを使ってもできるのでは？とやってみました。
{ EXCLUDE [差額理由] : sum( if [報告フラグ] >0 then [差額] else 0 end )}
これだと報告月の差額だけが集計されます。なぜかというと、クエリパイプラインの順序が影響しています。
差額理由を表示するために設定した報告フラグがフィルターに入っているので、EXCLUDEよりも前にディメンションフィルタで集計する対象がフィルタされてしまうからです。
これまで数表をダッシュボードで使うことが多く、累計出すのに不要なデータも表示していましたが、LOD計算やヘッダーの表示をオフする、「属性」の活用等をすると見せたいデータに絞った表現ができることを技術課題の中で学びました。過去に作成したごちゃごちゃVizはこれから直していこうと思います。
差額分析用のダッシュボード
ブレットグラフ、ウォーターフォール チャート、差額理由の一覧、さらに全体の集計値を数字で表示するシートも作成してダッシュボードに配置しました。
Tableau Publicに公開中の差額分析用のダッシュボードでは、Ord8のQ1を参考にコンテナとパラメータとを使って累月と単月の表示も切替えられるようにしています。
今月はどうなの？累計だとどう？は良く聞かれることなので、両方の視点が確認できるとより次のアクションにつなげられるのではと思います。
また、サンプルデータは売上の予算、実績データでしたが、費用についても勘定科目や組織のデータがあれば同じように比較できますし、計画年度があれば前年度実績との比較も同じようにできます。
実務ではパラメーターで比較する計画を選べるようにして、前年比や当初VS修正を確認できるようにしていますが、思いついた時にササっと比較できるので便利に使ってます。
以上が今回作成した月次差額分析用ダッシュボードの概要です。
同じような予実管理されているところは少ないかもしれませんが、Excelで管理しているデータでも、ファイルを集めてデータソースとしてまとめるとTableauでこんな分析できるんだ～という参考になれば幸いです。
この記事が気に入ったらサポートをしてみませんか？