この記事は 『CRESCO Advent Calendar 2018』 15日目の記事です。
システムズエンジニアリングセンターの垣屋です。
今回は日付関数を使用した2つの計算方法についてご紹介します。
- 前年同月の売上増減率の算出
- 1、2、3週間単位のサイクルで日付をスライドする
レポートやグラフを作成するにあたり、日付を使用した計算フィールドを作成する頻度は高いと思います。
日付のディメンションは軸やフィルタなど、様々な使い方ができる便利なディメンションです。
実際にプロジェクトで私が作った計算の考え方を使用し実装しています。
サンプルスーパーストア.xlsxを使用します。
※Tableau Desktopをインストールするとデフォルトで使用できるデータソースです。
マーケティングの指標の中でよく使用される前年同月の増減率算出方法が下記の式です。
(今年の売上 / 前年の売上) – 1
前年同月の売上増減率は前年データと比較しデータの傾向を分析するために用います。
今年の売上データと前年の売上データの同月データを比較し、
算出にはDATEDIFF関数を使用します。
以下に示す表のように、今年の売上と前年の売上の計算フィールドを作成します。

まず、今年と前年の売上を求める前の準備として最新のオーダー日の計算フィールドを作成します。
最新オーダー日はLOD計算を使用して取得します。
LOD式の中のMAX([オーダー日])で最新のオーダー日を取得することができます。
{fixed :MAX([オーダー日])} |

LOD計算を使用する目的として、後に使用するDATEDIFF関数の中で集計関数を用いることができないためです。
LOD計算を使用することで、非集計として扱うことが可能になります。
次に最新オーダー日の月を取得しておきます。
INT(MONTH([最新オーダー日])) |

ここまでで、今年と前年の売上の計算フィールドを作成する準備が整いました。
DATEDIFF関数を使用し今年の売上を集計していきます。
最新オーダー日とオーダー日の差分が-11~0である今年の売上を算出します。
(今回のデータソースは最新オーダー月が12月のため結果が-12となります)
IF |
DATEDIFF('month',[最新オーダー日],DATE([オーダー日])) > - [最新オーダー月] |
AND |
DATEDIFF('month',[最新オーダー日],DATE([オーダー日])) <= 0 |
THEN |
[売上] |
ELSE |
NULL |
END |

前年の売上も同様に作成します。
前年は今年から-12カ月で算出します。
IF |
DATEDIFF('month',[最新オーダー日],DATE([オーダー日])) > - [最新オーダー月] - 12 |
AND |
DATEDIFF('month',[最新オーダー日],DATE([オーダー日])) <= - 12 |
THEN |
[売上] |
ELSE |
NULL |
END |

これで今年の売上と前年の売上の計算フィールドを作成することができました。
最後に、前年同月の売上増減率を求めていきます。
前年同月の売上増減率の算出は冒頭の式で求めることができます。
(SUM([今年の売上]) / SUM([前年の売上])) - 1 |

オーダー日の月と前年同月比を列と行に配置すると前年同月の売上増減が表示され、
[書式設定]→[ペイン]→[数値]→[パーセンテージ]
を選択するとパーセンテージ表示すると増減率を表示することができました!


グラフにするとこのような感じになります。

今年と前年を比較しどれくらい売上が増減したかの
指標としてみせるときにとても有効となります。
プロジェクトでは、特定の期間を任意のサイクルで見るケースがありました。
しかし、Tableauの日付の表示形式は日、週、月のサイクルで見ることしかできません。
そこで、1週単位、2週単位、3週単位でスライドさせる日付の表示形式をTableau上で表現しました。
表示サイクルパラメーターでN週間単位で値を表示するかを切り替えます。
表示サイクルに対応するタームで日付をスライドして値を表示させます。

表示サイクル用のパラメータを作成します。
名前:表示サイクル |
データ型:整数(パフォーマンス面で整数型の方が文字列型より速くなるためです) |
値:1,2,3 |
表示名:1週間,2週間,3週間 |

表示するタームの日付フィルタを作成します。
DATENAME関数でオーダー日の週数を取得します。
- 1週間サイクルの場合は週数を取得します。
- 2週間サイクルの場合は1週間の週数を1/2することで14日間の値を取得することができます。
- 3週間サイクルの場合は1週間の週数を1/3することで21日間の値を取得することができます。
ただし、3週間サイクル表示は先に‐2をしてタームの開始日を合わせておきます。
そのあとに1週間を1/3しますがタームが0になってしまうので+1をしておくことが必要になります。
CASE [表示サイクル] |
WHEN 1 THEN STR(ROUND(INT(DATENAME('week',[オーダー日])))) + ' ターム' |
WHEN 2 THEN STR(ROUND(INT(DATENAME('week',[オーダー日])) / 2)) + ' ターム' |
WHEN 3 THEN STR(ROUND((INT(DATENAME('week',[オーダー日])) - 2) / 3) + 1) + ' ターム' |
END |

これで、第Nタームをフィルタに配置し、パラメータを表示すると操作が可能になりました。
表示サイクルのサンプルです (欠損データは表示なし)
・1週間サイクル(7日間表示)

・2週間サイクル(14日間表示)

・3週間サイクル(21日間表示)

デモはこのようになります。

表示サイクルパラメーターで表示する期間を選択し、
スライドを動かすと一定の期間で値を表示することができました。
今回は日付関数を使った2つの計算方法についてご紹介しました。
実際にプロジェクトでも、どうやって表現するのか内部で検討し、今回のように実装しました。
2つの計算方法を組み合わせたVizはTableau Publicにも公開していますのでぜひ参考にしてみてください。
<CRESCOデータ分析サービスの紹介ページはこちら>
https://www.cresco.co.jp/service/data_analytics/