3本目の記事は
・Tableauの計算タイプとよく使う関数の具体例
について、記事にしたいと思います。
前に2本、記事を書いてますのでよければ見てください。
１．Tableauの計算タイプの種類と概要
1.1 Tableauには3つの計算の種類がある
LODが特殊な計算をしていることは理解しやすいと思いますが、Tableauの計算を大きく分けるとこの三種類あるのだなと思っています。
| 種類 | 何を見て計算するか | 代表例 | 具体内容 |
|---|---|---|---|
| 通常の関数（行レベル） | 元データの行単位 | DATEDIFF('day',[オーダー日],[出荷日]) | 注文データの1行ごとにオーダー日と出荷日の差を計算する |
| 通常の関数（集計） | Vizの粒度に集約されたデータ | SUM([利益]) / SUM([売上]) | カテゴリをVizに置いていれば、各カテゴリについて売上と利益を合計してから利益率を計算 |
| LOD計算 | LOD式で指定する粒度 | { FIXED [顧客ID] : SUM([売上]) } | Vizがカテゴリ別でも地域別でも、まず顧客ID単位で売上を集計した値を作る |
| 表計算 | Viz上に作られた集計結果の表 | RUNNING_SUM(SUM([売上])) | まず月ごとに売上をSUMし、その後、Viz上の月次売上を順番に足していく |
２．それぞれの計算の解説
2.1 通常の関数（行レベル）
解説
具体例の「注文してから出荷するまで何日かかったか」を計算している
DATEDIFF('day',[オーダー日],[出荷日])
の様な関数が該当し、この関数は、元データの1行ずつに対して計算されます。
例えば
| オーダーID | オーダー日 | 出荷日 |
|---|---|---|
| A001 | 1/1 | 1/3 |
| A002 | 1/5 | 1/9 |
| A003 | 1/10 | 1/11 |
なら、各行で出荷日数が計算されて
| オーダーID | 出荷日数 |
|---|---|
| A001 | 2日 |
| A002 | 4日 |
| A003 | 1日 |
と計算されます。
2.2 通常の関数（集計）
解説
具体例の「売上と利益から利益率」を計算している
SUM([利益]) / SUM([売上])の様な計算をする時に利用する関数が該当し
Vizの粒度でデータを集計した値を使って利益率を計算します。
例えば
| カテゴリ | 売上 | 利益 |
|---|---|---|
| 家具 | 100 | 10 |
| 家具 | 200 | 30 |
| 家電 | 300 | 60 |
だった場合で、カテゴリがVizの粒度だった場合
| カテゴリ | SUM(売上) | SUM(利益) | 利益率 |
|---|---|---|---|
| 家具 | 100+200=300 | 10+30=40 | 40 / 300 = 13.3% |
| 家電 | 300 | 60 | 60 / 300 = 20% |
となります。
粒度をサブカテゴリにした場合、サブカテゴリごとに再集計されます。
2.3 LOD計算
解説
具体例の「Vizがカテゴリ別でも地域別でも、まず顧客ID単位で売上を集計した値を作る」の様に、Vizの粒度とは別に「この粒度で計算する」と指定した粒度の集計値を作ることができます。
LOD計算は3種類あり、簡単な説明は以下の通りです。
FIXED ：Vizの粒度とは独立して、指定した粒度で計算
INCLUDE ：Vizの粒度に指定ディメンションを追加して計算し、その結果をVizの粒度に再集計
EXCLUDE ：Vizの粒度から特定ディメンションを除いて計算
LODの仕組みや詳細な解説は、他の参考になる記事があるのでそもそもの考え方についてはそちらを参考にしてもらうとして
私の記事では、なぜLOD計算が必要になるのか、具体的な利用例を紹介して理解を深めたいと思います。
FIXED
①顧客の購入回数別に顧客数を見たい
この問いに答えるためには、以下の2つの計算を一度に実施する必要があります
（１）顧客ごとの購入回数を求める
（２）購入回数を軸にして、顧客数を数える
顧客ごとの購入回数を表示するだけなら、顧客IDごとにオーダーIDをCOUNTDすればできます。ただ、「購入回数が1回の顧客は何人、2回の顧客は何人か」という分布を作るには、まず顧客ごとの購入回数を計算し、その結果を新たな軸として顧客数を再集計する必要があります。
この部分について、FIXEDを使うことで購入回数別の顧客数という、通常の集計だけでは作りにくい可視化を実現できる訳です。
顧客別の購入回数という計算フィールドを作り
{ FIXED [顧客ID] : COUNTD([オーダーID]) }
ディメンションにして列に配置、顧客IDをカウント（個別）にすることでやりたい以下の集計ができます。
以下の様な2つの集計要件が含まれているものは今回のパターンと同じものになります。
・顧客の購入金額別に顧客数を見たい
・顧客の購入経過日数別に顧客数を見たい
②Vizの粒度に関係なく、全体の集計値を保持できる
これだけだと何を言っているのかよくわかりませんが
FIXED計算は通常
{ FIXED [ディメンション] : 集計関数([メジャー]) }
で書きますが
{ FIXED : 集計関数([メジャー]) }
と書くこともできます。
これができると何がうれしいのかというと、上記の通り、Vizの粒度に関係なく、全体の集計値を保持できる点です。
例えば、全体の売上を
{ FIXED : SUM([売上]) }
で定義し
SUM([売上]) / MIN([全体売上])
といった計算フィールドを持つことで、Vizの粒度がカテゴリ別でも地域別でも全体の売上に対する指定の粒度の売上の割合を計算することができます。
なお、上記計算であれば簡易表計算の「全体に対する割合」で実現可能ですが、表計算はフィルターで除外された条件があると絞り込み後の売上を分母として再計算されるため、フィルター後も全体に対する割合を表示したいといった要件の場合はこのやり方が適していると思います。
実際の例を記載すると
・市区町村別の売上の全体に対する割合を表示
・数が多いのでTOP10に絞りたいとする。簡易計算で割合を表示している場合、フィルターでTOP10に絞るとそのTOP10内の売上合計が分母になり、割合が計算される。
・一方、FIXED計算（SUM([売上]) / MIN({ FIXED : SUM([売上]) })）を行に置いた場合は、分母が全体の売上合計のままなので、TOP10でフィルターをかけても割合の値が変わらない（1枚目と同じ値になっている。ただし、コンテキストフィルターは除く）
INCLUDE
やりたい事が、オーダーID毎の平均売上を、カテゴリ別や地域別といった様々な視点で見たいという要件だったとします。
その場合に最も適したやり方が、INCLUDEです。
INCLUDEの計算はFIXEDでも作れます。
例えば、カテゴリ別にみる場合は
{ FIXED [カテゴリ], [オーダーID] : SUM([売上]) }
の様に書けばいいですし、地域×カテゴリ別に見るときは
{ FIXED [地域], [カテゴリ], [オーダーID] : SUM([売上]) }
と書けば可視化はできます。
ただ、計算フィールドを毎回修正しなくても
{ INCLUDE [オーダーID] : SUM([売上]) }
と書いて、カテゴリや地域をVizに配置すればそのディメンションを追加した粒度で計算し、その結果をVizの粒度で再集計してくれる機能です。
また、クエリパイプラインの違いもあります。
ディメンションフィルターはFIXEDに効きませんが（FIXEDの方が前に評価される）、INCLUDEには効きます。こういった違いを理解して利用する必要があります。
EXCLUDE
EXCLUDEは使い所は結構難しいですが、現在のViz粒度から指定したディメンションを除外して計算するLODです。
そのため、カテゴリ×サブカテゴリのVizからサブカテゴリを除外してカテゴリ単位の値を求めるなど、結果として「1つ上の粒度の値」を取得する用途によく使われます。
こちらも具体的な例を見ていきましょう。
下図は、サブカテゴリ別に利益の分布を地域ごとにプロットしたものです。
ここでサブカテゴリの横に利益の合計を表示したいニーズがあるとします。
現在のVizの粒度はサブカテゴリ×地域になっているため、単純に利益の合計を置くと下図の様に行がサブカテゴリ×地域ごとに分かれてしまいます。
そこで以下のEXCLUDE式で地域を除いた利益の合計を計算します。
{ EXCLUDE [地域] : SUM([利益]) }
別の事例としては、階層構造になっているディメンションについて上の粒度と下の粒度の値を比べるといったこともできます。
具体的には、サブカテゴリ別の各利益とこのサブカテゴリが所属するカテゴリの利益との比較といった形です。
カテゴリ全体の売上と利益をEXCLUDEで
{ EXCLUDE [サブカテゴリ] : SUM([売上]) }
{ EXCLUDE [サブカテゴリ] : SUM([利益]) }
とし、利益率を
MIN([カテゴリ利益]) / MIN([カテゴリ売上])
の計算フィールドを作成します。
作成したカテゴリごとの利益率を詳細において、リファレンスラインを引くことで、サブカテゴリの利益率 vs そのサブカテゴリが属するカテゴリ全体の利益率といったグラフが作成できます。
2.4 表計算
概要
表計算は、元データを計算するのではなく、TableauがVizを作るために集計した結果を、さらに計算する機能と考えると分かりやすいと思います。
つまり、例えば下図の様な月別の売上を可視化したVizがあるとして
このVizを右クリックしてデータの表示を押すと以下の様な集計表が表示されます。Tableauはこのデータを基に可視化するVizを作っている訳です。
ここで表計算の1つである「RUNNING_SUM(SUM([売上]))」を行に足すと、簡易表計算でもできる累計の売上が可視化されます。
ここで再度、データの表示を押すと以下の表な集計表になっており、RUNNING_SUMは月別の売上を順次合計していっていることが解ります。
つまり、計算の流れとしては、
元データ
↓
月別にSUM([売上])
↓
2013年1月 100000
2013年2月 150000
2013年3月 200000
…
↓
表計算（RUNNING_SUM）
↓
2013年1月 100000
2013年2月 250000
2013年3月 450000
という順番で計算していることが解ります。
なお、メジャーを右クリックすると簡易表計算（累計、差、割合…）が表示されると思いますが、これは表計算そのものであり、よく使うものをTableauがあらかじめ用意してくれている機能と考えることができます。
3．よく使う関数の解説
3.1 行レベルでよく使う日付関数
関数については全てを覚える必要はないと思いますが、よく使うものについては覚えておいて損はないと思います。特に以下の日付系の関数はよく使うので覚えておきたいです。
DATEPARTとDATETRUNCが混同しやすいですが、前回の記事で書いた日付の違いと紐づいていて、日付ピルで選択する上側／下側の日付が、DATEPART／DATETRUNCにそれぞれ対応しています。
| 関数 | 一言でいうと | 関数の式の例 | サンプルスーパーストアでの例 |
|---|---|---|---|
DATEDIFF |
2つの日付の差を求める | DATEDIFF('day', [オーダー日], [出荷日]) |
注文から出荷までの日数 |
DATEADD |
日付を指定した期間だけずらす | DATEADD('day', 30, [オーダー日]) |
注文日の30日後 |
DATEPART |
日付の一部分を数値で取り出す | DATEPART('month', [オーダー日]) |
注文月を1～12の数値で取得 |
DATETRUNC |
日付を指定した粒度の先頭に揃える | DATETRUNC('month', [オーダー日]) |
注文日を月初の日付に変換 |
TODAY / NOW
|
現在日・現在日時を取得する |
TODAY() / NOW()
|
最終購入からの経過日数を計算する際の基準日 |
3.2 よく使う集計計算の関数
集計計算についてもよく使う関数について例を記載しておきます。
| 関数 | 一言でいうと | 関数の式の例 | サンプルスーパーストアでの例 |
|---|---|---|---|
SUM |
数値を合計する | SUM([売上]) |
カテゴリ別の売上合計 |
AVG |
数値の平均を求める | AVG([割引率]) |
カテゴリ別の平均割引率 |
COUNT |
NULL以外のデータ行数を数える | COUNT([オーダーID]) |
カテゴリ別の注文明細行数 |
COUNTD |
重複を除いた値の種類数を数える | COUNTD([オーダーID]) |
カテゴリ別の注文数 |
MIN |
最小値・最も古い日付を取得する | MIN([オーダー日]) |
顧客別の初回注文日 |
MAX |
最大値・最も新しい日付を取得する | MAX([オーダー日]) |
顧客別の最終注文日 |
3.3 LOD計算
※LODは2．で説明した通りなので割愛します
3.4 よく使う表計算の関数
簡易表計算で大体のことは事足りるので細部まで覚える必要はないと思いますが、次の3種類は簡易表計算でもよく使われているので、中身を理解しておいて損は無さそうです。
| 種類 | 一言でいうと | 関数の式の例 | サンプルスーパーストアでの例 |
|---|---|---|---|
RUNNING_XXX |
先頭から現在位置までを計算する | RUNNING_SUM(SUM([売上])) |
月次売上の累計 |
WINDOW_XXX |
指定した範囲のマークをまとめて計算する | WINDOW_AVG(SUM([売上]), -2, 0) |
3か月移動平均 |
LOOKUP |
現在位置から前後のマークを参照する | LOOKUP(SUM([売上]), -1) |
前月売上の取得・前月比 |
RUNNING系は概要で記載した通りなので、WINDOW系の説明に入ります。
WINDOW系はViz上のマークのうち、指定した範囲をまとめて計算する関数です。
WINDOW_AVG(集計式, 開始位置, 終了位置)という形で記載します。
例えば、移動平均に用いられており、3か月の移動平均であれば
WINDOW_AVG(SUM([売上]), -2, 0)
といった形で記載します。
具体的な使われ方の説明として、RUNNING_SUMとWINDOW_SUMを組み合わせると、上位何サブカテゴリで全体の売上の80％を占めるかといったパレート分析ができます。
・サブカテゴリ別の売上を降順で並べ替え
・以下の式を行に記載
RUNNING_SUM(SUM([売上]))
/
WINDOW_SUM(SUM([売上]))
上記記載は、WINDOW_SUMは開始と終了を指定しないと全体を足すので、全体のうちの累計で足されているものの割合といった計算ができています。
最後にLOOKUPです。
現在位置から何個前・何個後ろのマークを参照するかという関数です。
LOOKUP(集計式, オフセット)という形で記載します。
例えば、LOOKUP(SUM([売上]), -1)の様に記載し、1つ前のマークを取得します。
現在が4月なら3月の値を取得することができ、4月の位置で3月の売上を取得できるということです。
具体的な使われ方の説明として、簡易表計算でどう使われているか見たいと思います。
以下の図は、月ごとの時系列の売上推移のグラフです。
これに行で合計(売上)を複製し、簡易表計算で差の割合を指定します。
こうすることで前月から何％売上が変化したか売上差の比を見ることができます。
ここで簡易表計算がLOOKUPをどう使っているのか見てみたいと思います。
(SUM([売上])) - LOOKUP((SUM([売上])), -1) ← 現在の売上から前月の売上を引いて、いくら売上が変わったかの差を計算
/ LOOKUP(SUM([売上]) ← 前月の売上で割って、前月比を計算
※ZNはNULLなら0を返す関数、ABSは絶対値を返す関数なので説明割愛
この様に、前月比や前年比といった計算に良く用いられていると思います。
以上、同じような点で理解が曖昧な方の参考になれば幸いです。