今回は実務寄りのネタです。進捗管理や実績vs目標vs予測に関するダッシュボードを作ります。
KPI管理のための定点観測を念頭に置いています。
（実はもうひとつテーマがあるのですが、それは記事の最後の方で）
今回使用するデータはTableau 2019.3版のSuperStoreです。
また以下にダミー予算データを用意しました（手抜きでPaper分のみです）。
https://docs.google.com/spreadsheets/d/1mFanRaEalF0iqUk8aBEdmWlK7bFVB6Qn1yhZQmKgd2w/edit?usp=sharing
今回の記事に使用したWorkbookは以下からダウンロードできます。
注意
今回はData Blendingを使用します。
この辺りの設定や、どの計算式がどちらのデータソースに属するのかはWorkbookを見るのが一番早いです。
したがってダウンロードを強くおすすめします。
今回のチャレンジ
この記事で主に取り扱うテーマは、以下の可視化のような「実績、前期間、目標、着地予測すべて見せる」場合です。
今回のケースでは、月間の売上管理を想定します。
当月の売上を日別で追いながら、その数値が
前月と比べどう推移しているか
目標に対する進捗はどうか
着地予測はどこか
を見えるようにしています。ここで着地予想は過去30日間売上の30日平均を元に算出しています。
ここでそれぞれの計算式は基本的には簡単なのですが、この「着地予想」だけ少々ステップを要します。
今回の記事では全体的な作り方をお見せしつつ、着地予想の出し方、見せ方について述べます。
ということで着手していきましょう。
計算をつらつら作る
まず初めに、レポート表示の基準日に関する計算式を作成します。
Basis Date (Date) (予算シートとSuperstoreそれぞれのデータソースで作成)
CASE [Basis Date]
WHEN ''
THEN TODAY() - 1
ELSE DATE([Basis Date])
END
ここで Basis Dateは文字型パラメータです。未入力のときに前日を返すような実装をしています。
（Dynamic Parameterの疑似実装みたいな感じです）
今回はBasis Dateが最新日付、という想定とします。
したがってBasis Date以上のデータは表示されてほしくないため、表示日付のフィルターを作ります。
Date Filter
[Order Date] <= [Basis Date (Date)]
目標値に関する計算
まず最初に、当月の目標値の日累計を出します。
そのために当月の目標値を取得する計算式を書きます。計算式を作成するのは予算シートのデータソースです。
Current Target
IF [Month of Order Date] = DATETRUNC('month',[Basis Date (Date)])
THEN [Target Sales]
END
こちらを日割りします。
Current Month's Daily Target
MIN([Current Target])
/
MIN(DAY(DATEADD('month',1,[Month of Order Date]) - 1))
MIN()に特に意味はなく、Data Blendingの都合で集計値にしているというだけです。
分母について「DATEADD('month',1,[Month of Order Date]) - 1」にて、目標月の最終日を取得します（前提として予算シートはその月の1日が入力されています）。
その最終日のDAY()を取ることで、最終日の日付を取得できます。
この日割り目標を、Superstoreのデータソース上で累計させます。
End of Current Month
DATEADD('month',1,DATETRUNC('month',[Basis Date (Date)])) - 1
Running Total of Daily Target
IF MIN(DAY([Order Date])) <= DAY([End of Current Month])
THEN RUNNING_SUM([Target].[Current Month's Daily Target])
END
上記計算のIF文について、これは累計をその月の最終日超えて表示させないためです。
さて、ここまでをSheet上に配置してみましょう。以降Basis Dateのパラメータには「2019/11/20」を入れておきます。
（簡単のためSub-Category = Paperのコンテキストフィルターを使用。データソースフィルターでも良いです）
さて、次に当月実績と前月実績を入れていきます。
当月実績と前月実績を作る
はじめに、当月と前月の日付期間にラベル付けをします。
Month Label
CASE DATEDIFF('month',[Order Date],[Basis Date (Date)])
WHEN 0 THEN 'Current Month'
WHEN 1 THEN 'Previous Month'
END
DATEDIFF()により、月単位での差をとっています。
もしBasis Dateと同じ月であれば０、前月であれば１を返すので、それぞれにCurrent Month, Previous Monthのラベルを付けています。
このラベルを元に当月と前月数値を作成します。
Current Sales
IF [Month Label] = 'Current Month' THEN [Sales] END
Previous Sales
IF [Month Label] = 'Previous Month' THEN [Sales] END
それぞれの累計値をSheetに追加してみます。
まあ概ねやりたいことは出来ているのですが、当月の累計値がいまいちですね。
Basis Dateを11/20にしたので、21日以降は表示させたくないなと。
ちなみにどうして21日以降のマークがあるかというと、過去で21日という日付をもったデータから来ています。
過去データの「21日という日付を持つ」データには、Current SalesはNULLで入っています。
しかしRUNNING_SUMで累計を順々に取っているので、NULL値を順々に足した累計値（実質的には11/20までの累計値）を表示させています。
ということで、線マークの表示をコントロールします。
Current Running Total
IF MAX(DAY([Order Date])) <= DAY([Basis Date (Date)])
THEN RUNNING_SUM(SUM([Current Sales]))
END
条件文はシンプルで「Basis Dateの日付以下で累計値を表示」というだけです。
実際に表示させてみると、以下のようになります。
最後に着地予測を表示させます。
着地予測の計算を作る
今回の着地予想では以下の計算をします。
着地予想＝Basis Dateまでの当月累計値＋過去30日平均×Basis Dateからの経過日数
当月累計値はすでに作成できているので、過去30日平均とBasis Dateからの経過日数を計算します。
まずは30日平均を算出するために、30日間の累計Salesを出します。
Past 30 days
DATEDIFF('day',[Order Date],[Basis Date (Date)]) <= 29
AND
DATEDIFF('day',[Order Date],[Basis Date (Date)]) >= 0
上記を使って過去30日分のSales総計を計算します。
30-day total sales
{SUM(IF [Past 30 days] THEN [Sales] END)}
LOD計算の解説記事で述べたように、FIXEDはSheetのつくりに独立して値を返すので、30-day total salesは常に過去30日間の累計Salesを返します。
こちらを30で割れば30日平均が出ます。
次にBasis Dateからの経過日数を計算します。
Days from Basis Date
MIN(DAY([Order Date])) - DAY([Basis Date (Date)])
ここでMIN()、つまり集計値を使用しているのは、Sheet上のデータがDAY(Order Date)で分けられているからです。
言い換えればViz-LODがDAY(Order Date)のため、各マークでのDAY(Order Date)の集計値はMIN、MAX、AVG、ATTRいずれでも必ず一意です。したがって適当な集計を用意しました。
これら値を使用して着地予測を計算します。
Expected Sales at EoM
IF MIN(DAY([Order Date])) > DAY([Basis Date (Date)])
AND MIN(DAY([Order Date])) <= DAY([End of Current Month])
THEN RUNNING_SUM(SUM([Current Sales]))
+ MIN([30-day total sales])/30
* [Rest days]
END
IF文についてはCurrent Running Totalと同様に、表示制御のためです。
下記画像のように配置し、二重軸と軸の同期をします。
書式設定などを整えてあげると冒頭のようにできます。
ということで、今回のテーマ「実績vs目標vs予測」は完了しました
さて今回の裏テーマですが「マークの表示制御」です。
計算式の中で、IF文を駆使して「ある条件のときは値を返し、それ以外ではNULLを返す」制御をさせていました。
NULL値はSheet上では表示されなくなるので「計算は裏で走るがTableau上は何も見えない」という挙動をさせられます。
Tableau上でマークを隠す方法はいくつかあるのですが、今回のように計算式で表示制御もできますよ、という観点も持っておいて役立つかなと思います。
最後に、ダッシュボード上に付加情報を足してあげることで、今回の進捗管理ダッシュボードは完成とします。
作りは簡単なので、ぜひWorkbookをダウンロードし見てみてください。
最後に
最近がっちりした解説系記事が多かったので、今回は比較的ライトなテーマを扱ってみました。
こういう進捗管理系のダッシュボードはお題として良く来ると思います。
今回は簡単な予測ロジックを用いましたが、このあたりはビジネス要件に合わせて適宜作成してください。
ご質問等はTwitterまたはLinkedinまでよろしくお願いします。 それでは。