新年あけましておめでとうございます。今年もよろしくお願いいたします。
今回はタイトルの通り、日次の（累計でない）YoYを扱います。
今回使用したWorkbookはこちらにあります。
また今回はこちらで作成した日次データを使用するのですが、使用したデータは以下に格納しました。
ちなみに生成に使用したコードは以下です。
date = seq(as.Date("2011-01-01"), as.Date("2021-12-31"), by = "day");
sales = round(runif(length(date),70,100));
data = data.frame(date, sales)
write.csv(data,file = "sampledata.csv")今回は表計算を多用します。
簡単のためディメンションの少ないデータを使用していますが、実際にはSheetに複数ディメンションを入れる、つまり適切な表計算設定について考え確認する必要が出てくると思いますので、もし本記事がお手元のデータで上手く動かない場合、以下の記事を参考に表計算設定を確かめてください（特にINDEX()を用いて、表計算が望ましい方向で走っているか確認してください）。
日次で見るYoYとそのチャレンジ
まず初めに、そのまま前年同日と当日を比較するケースを考えます。
これはシンプルにLOOKUP()で-365を指定しているのですが、１年前の数値を持ってきて当日と比較しています。
ただこのような実装には以下のような問題点があります。
うるう年に対応できない（2/29日のデータをフィルターしないとLOOKUPがずれる）
もしデータに曜日や休日による傾向差がある場合、その影響を考慮できないので単純比較は好ましくない
そもそも日次YoYはノイズが大きく出る
ということで今回は日次YoYを見るためにはどのような実装が良いのか？ということを考えていきます。
方法１：うるう年対応した日次YoY
まずうるう年について考えます。
まずそのままLOOKUPに-365を使用した場合、どのような状態が起きるかを考えます。
そのままLOOKUP
LOOKUP(MIN([Date]), -365)
上図では上が当年の日付、下が「そのままLOOKUP」で取得した日付です。
うるう年の2/29以降
うるう年翌年の2/28まで
取得する日付がずれていることが分かります。
これはうるう年によって、本当は366日前を取得しなければいけない日が生じるからです。
具体的に言えばうるう年の2/29以降と、うるう年翌年の2/28までですね。
ということで、以下の計算式を実装します。
前年同日 (うるう年対応)
LOOKUP(
MIN([Date])
,IF (MIN(YEAR([Date]))%4 = 0 AND MIN([Date])
>= MAKEDATE(MIN(YEAR([Date])), 2,29))
OR (MIN(YEAR([Date]))%4 = 1 AND MIN([Date])
<= MAKEDATE(MIN(YEAR([Date])), 2,28))
THEN -366
ELSE -365
END
)
上図では上が当年の日付、下が「前年同日 (うるう年対応)」で取得した日付です。
確かに2/29日以外は前年同日が取得できるようになりました。
ちなみに2/29の比較対象を3/1にしたい場合は、MAKEDATE(MIN(YEAR([Date])), 2,29))をMAKEDATE(MIN(YEAR([Date])), 3,1))に変えればOKです。
ということで、上記のアイデアを基に日次YoYを以下で計算できます。
YoY (うるう年対応)
SUM([Sales])/
LOOKUP(
SUM([Sales])
,IF (MIN(YEAR([Date]))%4 = 0 AND MIN([Date])
>= MAKEDATE(MIN(YEAR([Date])), 3,1))
OR (MIN(YEAR([Date]))%4 = 1 AND MIN([Date])
<= MAKEDATE(MIN(YEAR([Date])), 2,28))
THEN -366
ELSE -365
END
) - 1
注：うるう年の条件について
本当はうるう年は４で割り切れる数という条件以外にも条件があるのですが、例えば2100年までTableauを使い続けている人類が想像できないので、本記事では割愛します。
逆に2100.x versionのTableau見てみたいですが、それは技術革新が起きていないのかTableauが常にトップを走り続けた世界戦なのか…
方法２：約１年前の同曜日を使用したYoY（曜日調整）
例えばですが、自分のブログは平日によく見られる傾向があります。
曜日や休日によって数値傾向が異なるデータについて、前年同日比(YoY)や前月同日比(MoM)をそのまま計算することは、必ずしも本来知りたい「成長度」を出してくれない場合があります。
ここで考えるのは、同日だからと言って今年の平日と昨年の休日を比較対象として使用したくはないというようなケースです。
ということで大体1年前の同じ曜日を持ってきて、それと比較する形で曜日による影響をなくした形で見ようというのが、ここでのモチベーションです。
今回は簡単のため、祝日は無視します。
曜日調整のための計算式
1年間は概ね52週間なので、約1年前の同じ曜日を取得するために以下の計算フィールドを作成します。
約1年前の同曜日
LOOKUP(MIN([Date]),-52*7)
例えば2012/1/1は52*7 = 364日前である2011/1/2を約1年前の同曜日として参照しています。
こちらを応用すれば、約1年前の同様日の数値（今回は売上）を取得できます。
約１年前の同曜日のSales
LOOKUP(SUM([Sales]),-364)
YoY (曜日調整)
SUM([Sales])/[約１年前の同曜日のSales] - 1
上図のように、大体１年前の同じ曜日の日付の数値を取得しYoY計算していることが分かります。
ということで曜日調整YoYが上記のように計算できました。
方法３：前年同日のN日間移動合計を使用したYoY
ところで、ここまでのYoYグラフを見て分かるように、基本的に日次YoYそのままでは結構ノイズが大きくなるのではと思います。
（今回のサンプルデータは一様乱数で数値を作成しましたが、そうでなくても日次YoYが滑らかなカーブを描くことは少ないのではと）
ということで、ノイズを丸め込む文脈で前年同日と当日のN日移動合計を比較する形で日次YoY評価を行う形を考えます。
（このあたりは単純N日移動合計以外にも、R/Python連携で時系列分析の手法を使う方法も良さそうですが、今回は簡単な実装のみ取り扱いますということで）
ということで、方法１で取り上げた計算を元に移動合計を計算していきます。
以下「N日移動平均」は整数型パラメータです。
WINDOW start
IF (MIN(YEAR([Date]))%4 = 0 AND MIN([Date])
>= MAKEDATE(MIN(YEAR([Date])), 2,29))
OR (MIN(YEAR([Date]))%4 = 1 AND MIN([Date])
<= MAKEDATE(MIN(YEAR([Date])), 2,28))
THEN -366
ELSE -365
END
- ([N日移動平均] - 1)
WINDOW end
IF (MIN(YEAR([Date]))%4 = 0 AND MIN([Date])
>= MAKEDATE(MIN(YEAR([Date])), 2,29))
OR (MIN(YEAR([Date]))%4 = 1 AND MIN([Date])
<= MAKEDATE(MIN(YEAR([Date])), 2,28))
THEN -366
ELSE -365
END
前年同日N日移動合計
WINDOW_SUM(SUM([Sales]),[WINDOW start],[WINDOW end])
YoY (移動合計)
WINDOW_SUM(SUM([Sales]),-([N日移動平均]-1),0)
/ [前年同日N日移動合計]
- 1
上図は２８日間移動合計の場合です。
この実装であれば日次YoYのノイズが軽減され、より大局的な傾向を見ることが可能です。
またパラメータの値が７の倍数であれば各曜日が同じ数だけ移動合計に含まれるので、曜日調整も内包する形になります。
ということで個人的には最後の方法をオススメしたいのですが、そこはユーザーのニーズや慣習に合わせてお使い下さい、ということで。
（一方でより良いデータの見方、見せ方を伝えていくことも大事ですよね）
ちなみに：欠損値のある場合
今回のサンプルデータは各日付に数値が欠けることなく入っている場合を想定しましたが、欠損値のあるデータではどのようにするべきでしょうか。
方法は大きく２つあるかなと思いまして、１つはTableauの「欠損値を表示」オプションを使用することかと思います。以下の記事を参照ください。
もう一つの方法は、SQLやTableau Prep等でカレンダーテーブルと結合させ欠損を埋める方法です。
こちらは数値テーブル側の作りによってはデータサイズが大きくなる場合がある一方、Tableauに整理されたデータを読み込ませることが出来ます。
どの方法を使うかはケースバイケースですので、ここでは紹介に留めます。
最後に
今回は日次YoYについて取り上げました。日次MoMについても考え方は同様ですが、こちらはもう少し計算式が複雑になりそう（月に含まれる日数に対する条件分岐が必要そう）なので、またいつか検証して記事にまとめます。
ご質問等はTwitter, Linkedinへよろしくお願いいたします。それでは。
Comments