こんにちは、エクスチュアの渡部です。
TableauのLODブログ第４弾です。
過去記事はこちらです。
・Tableau：分かりやすいLOD – 概要編
・Tableau：分かりやすいLOD – FIXED編
・Tableau：分かりやすいLOD – INCLUDE編
今回はLOD関数の中の、「EXCLUDE」について説明します。
過去記事でも繰り返している通り、LOD関数とは「集計の粒度を自由に操れる関数」です。
では、EXCLUDEは何が出来るのかというと、
ビューに配置された「集計に不要なディメンション」を除外して集計出来る関数です。
FIXED、INCLUDEとは構文が似ているだけで動きが違うので、FIXEDとINCLUDEのことは一旦忘れて読んで頂くのが良いと思います。
ビューに配置された「集計に不要なディメンション」とはどのようなシーンで生まれるのでしょうか。
例えば、下記のようにサンプルスーパーストアのデータを使って、[Sub-Category]ごとの[Sales]の合計」を算出したとします。
ところがここで、「各[Sub-Category]の売上が全体の売上に対してどのくらいの割合を占めるのかも一緒に出したい」（下図の赤枠を追加したい）となった場合、どうすれば良いでしょうか。
※表計算でも出せますが、EXCLUDEの理解のため一回忘れてください。。
全体に対する割合を追加するためには、
「①各Sub-Categoryの売上」÷「②全体の売上」という式が必要ですね。
①は何も問題なく出せますが、
②は行に「Sub-Category」が置かれているため、普通にSUM(Sales)だと
「Sub-Categoryごとの」売上合計という結果になってしまいます。
下図のように行にSub-Categoryがなければ、全体の売上合計が出せますね。
今回集計に使用したいのはこの結果なので、「全体の売上」の集計に「SubCategory」が不要な状態となっています。
こんな時にEXCLUDEが活躍します。
では、EXCLUDEでSub-Categoryを除外して、「全体の売上」を算出したいと思います。
……….
解説に入る前に、EXCLUDEの構文の説明です。
{ EXCLUDE [ビュー上の除外したいディメンション1](,[ビュー上の除外したいディメンション2]…) : [集計式] }
です。
[ビュー上の除外したいディメンション]は何個でも配置が可能です。
[集計式]には、ディメンションを除外した状態で何を集計したいのかによって記載を変えてください。
……….
では、今回のケースに構文を当てはめていきます。
「全体の売上」は今回のビュー上では、
[Sub-Category]を除いた状態で、「売上の合計」を出す必要があります。
そのため、{ EXCLUDE [Sub-Category]:SUM([Sales])}
と書きます。
これによって、行に配置されているSubCategoryをなかったものとして扱うので、「全体の売上」が算出出来ます。
ということで、「全体の売上の割合」は、
SUM([Sales]) / MIN({ EXCLUDE [Sub-Category]:SUM([Sales])})
になります。
※MINでEXCLUDEで囲っているのは、集計関数にするためです。
詳細は下記の「EXCLUDEの注意点」で補足しますが、あまり気にしないでください。。
あとは作成した計算フィールドをラベルに配置して終わりです。
＜例２：平均との差分を出す＞
続いては、こちらのグラフを見てください。
[Category]×[Sub-Category]ごとの売上合計が出ていますね。
ではこの各サブカテゴリの売上合計が、
各カテゴリ内の平均と比べて、どの程度の差があるのかを出したい（下画像の右側のようにしたい）場合、どのように出せば良いでしょうか？
※表計算でも出せますがEXCLUDEの理解のため一旦忘れてください。。(２回目)
今回必要な計算式は
「①各Sub-Categoryの売上合計」 -「②CategoryごとのSubCategoryの売上合計の平均」です。
①は既に出ているので、残りは、②です。
②を出すには、「カテゴリごとの売上合計」÷「サブカテゴリの個別カウント数」という式が必要ですが、
今回も求める結果に対して、行に配置されたサブカテゴリが不要な状態です。
ということで、今回もEXCLUDEで「Sub-Category」を除外して集計します。
式は以下のようになります。
{ EXCLUDE [Sub-Category] : SUM([Sales]) / COUNTD([Sub-Category])}
そして、今回出したいのは差分なので、
SUM([Sales]) – MIN({ EXCLUDE [Sub-Category] : SUM([Sales]) / COUNTD([Sub-Category])})
を列に配置します。
これで無事に平均との差分を集計することが出来ました。
※分かりやすいように、MIN({ EXCLUDE [Sub-Category] : SUM([Sales]) / COUNTD([Sub-Category])})の結果も一番右に配置しています。
Sub-Categoryがない状態で、SUM([Sales]) / COUNTD([Sub-Category])を集計しています。
——-
このようにしてEXCLUDEはビュー内のディメンションから狙った集計に邪魔なディメンションを除外出来ます。
今回は行や列のディメンションだけでしたが、マークカードに置いてあるディメンションも同様に操作できるので、是非試してみてください。
——-
・EXCLUDEの注意点
最後にEXCLUDEの注意点です。
① EXCLUDEを集計関数にする
EXCLUDEを集計関数として扱いたいのであれば、
AGG({ EXCLUDE [ビュー上の除外したいディメンション1](,[ビュー上の除外したいディメンション2]…) : [集計式] })
と集計関数でEXCLUDE式を囲ってください。
AGGには(MIN/MAX/SUM/ATTR)のいずれかを入れてください。
上記の例だとMINにしていますが、どれを選んでも結果は変わりません。
※デフォルトだとATTR(属性)が入ります
この「どれを選んでも変わらない」という点がEXCLUDEとINCLUDE/FIXEDの大きな違いです。
EXCLUDEはINCLUDEやFIXEDのように「集計の集計」が行える関数ではなく、{EXCLUDE ~~}の時点で結果が決まります。
（よく分からなければ、とりあえずSUMとかATTRとか入れれば良いんだな〜と覚えてください）
② ビュー内に配置されていないディメンションを宣言しても意味がない。
EXCLUDEはビュー内においてある邪魔なディメンションを除外する関数です。
ビュー内に配置されていないディメンションを宣言しても意味がありません。
② フィルターは除外出来ない。
「EXCLUDEはフィルターも除外可能？？」という発想が生まれそうですが、除外は出来ません。
理由は、INCLUDEやEXCLUDEはディメンションフィルターがかかった後に機能する関数だからです。
詳細はこちらへ
——-
・EXCLUDEは表計算が分かれば不要？
EXCLUDEは表計算系で代替可能な箇所が多いため、表計算が分かっていれば不要？と思いそうですね。
ただ、EXCLUDEは非集計関数としても扱うことが出来るという点が、集計関数である表計算とは大きく異なる点です。
例えば、このブログ で使用している下記の式、
{ INCLUDE [顧客名]: MIN(IF [オーダー日]= { EXCLUDE [オーダー日] : MIN([オーダー日])} THEN 1 ELSE 0 END) }
は、IF文の中で非集計の「オーダー日」と非集計結果であるEXCLUDE式を組み合わせて、
「顧客名」ごとの「最初の購買日なら１を返すという計算をしています。
これは、集計関数として扱われる表計算では出来ない技ですね。
このように活用の仕方次第でEXCLUDEにしかできない技が存在します。
（他にもEXCLUDEの活用例があれば教えてください！）
——-
エクスチュアは国内では希少なTableau Certified Professionalも在籍するTableauのパートナー企業です。
また、Google Cloud Platform、Adobe Marketing Cloudといったマーケティングテクノロジーに精通したスタッフが、
デジタルマーケティングからビッグデータ分析まで統合的にサポートします。
お問い合わせはこちらからどうぞ
ブログへの記事リクエストはこちらまで
