合計列に前年比を載せる方法
DATA Saber Bridge 3rdに挑戦中のTakuroです！
Tableauで集計表を作成するときに、「前年比も一緒に集計表に載せたいんですが、、」という相談、あると思います。
しかも今回の要件は
「直近3年分＆最新年の前年比を集計表に」
ということで、「メジャーネームやメジャーバリューでもできなくはないけど、別名の編集使うのは保守的に面倒だな、、」といった内容になります。
別シートで作ろうにしてもなかなか大変です。罫線が上下で揃わなかったり、わざわざラベルを作ったり、、、。
ということで、少々強引な方法で集計表に前年比も同時に載せてしまおうと思います。
完成イメージ
これを
こうします
実装手順
からくりとしては、「列の総計」を利用しています。
書式設定から合計や総計のラベルを自由に変更することが可能です。
では、ここからどうやって「列の総計」に前年比を表示させるのかですが、もちろん計算フィールドを使用します。
IF MAX(YEAR([オーダー日])) = MIN(YEAR([オーダー日]))
THEN SUM([利益])
ELSE
SUM(IF YEAR([オーダー日])={EXCLUDE [オーダー日]:MAX(YEAR([オーダー日]))} THEN [利益] ELSE 0 END)
/
SUM(IF YEAR([オーダー日])={EXCLUDE [オーダー日]:MAX(YEAR([オーダー日]))}-1 THEN [利益] ELSE 0 END)
END
計算フィールド解説
IF MAX(YEAR([オーダー日])) = MIN(YEAR([オーダー日]))
THEN SUM([利益])
IF文の条件式です。ここでは「合計列以外であれば利益を出してね！」という式になります。
集計表では年ごとに行が区切られているため、そこに含まれる年の種類は当然1種類になります。
COUNTD(DATEPART('year', [オーダー日]))=1 と同義ですが、パフォーマンスの観点からMAX・MINを使用しています。
ELSE SUM(IF YEAR([オーダー日])={EXCLUDE [オーダー日]:MAX(YEAR([オーダー日]))} THEN [利益] ELSE 0 END)
/
SUM(IF YEAR([オーダー日])={EXCLUDE [オーダー日]:MAX(YEAR([オーダー日]))}-1 THEN [利益] ELSE 0 END) では後半部分です。
合計列には本来「表示されているすべての行を合計」したものが入るはずですから、そこに含まれる年の種類は複数含まれるはずです。今回の場合、すべての年でデータが含まれるのであればCOUNTD(DATEPART('year', [オーダー日]))=4となります。
（注意！！ 最後のおまけを考慮したとて、1年分しかデータが含まれていない場合、この方法は使用できませんのでご注意ください）
ですが、集計表の行は日付（年）が入っているため、合計列も当然日付の粒度で集計されてしまいます。
そこでEXCLUDEを使用して、日付を集計の対象から除外する必要があるのです。
{EXCLUDE [オーダー日]:MAX(YEAR([オーダー日]))}
あとはこの計算フィールドをテキストに入れて、書式設定から総計（合計）だけパーセント表示にしてあげて、、、
これで集計表に前年比も同時実装できました。
おまけ
今回の方法だと、途中に歯抜けのデータがあるとうまく表示されません。
そこで、ZN+LOOKUPを使用したものが以下になります。
IF COUNTD(DATEPART('year', [オーダー日]))=1
THEN ZN(LOOKUP(SUM([利益]),0))
ELSE
ZN(
LOOKUP(
(SUM(IF YEAR([オーダー日])={EXCLUDE [オーダー日]:MAX(YEAR([オーダー日]))} THEN [利益] ELSE 0 END)
/
SUM(IF YEAR([オーダー日])={EXCLUDE [オーダー日]:MAX(YEAR([オーダー日]))}-1 THEN [利益] ELSE 0 END))
,0)
)
END
この記事が参考になりましたら幸いです！
「スキ」もお待ちしております！