【Sisense Formula】～ごとの平均、最大、最小ってどうやって求めるの？

週末は札幌にいたんだけど、北海道のとうもろこしが一番おいしい季節なもので、２本ほど持ち帰った。今朝の朝食はピュアホワイト（とうもろこしの品種）。
うーん、シフクの時間…
以前、道南を旅行した時に「とうきみあります」っていう看板を目にしたのを思い出した。トウキビの間違いじゃないかと思ったのだが、NAVERで調べると、北海道南部から青森、秋田、岩手では「とうきみ」と呼ばれているらしい。
宮城に行くと「とうみぎ」、広島県では「まんまん」...
とうもろこしは全国色んな呼称で食されているようだ。
つまりどこでも穫れるものとも言える訳だが、僕は全国どこを探しても、この時期の北海道のとうもろこしの右に出るものはないと思う。嘘だと思うなら北海道に行って食べてみてー
北海道の旅行・ツアー（うちのお客さんのサイト）

さて、今回は【Sisense Data Modeling】インポートクエリを使ってデータベースSQLでインポートするで作成したec_sql_unionのデータを使って、顧客ごとの注文金額を都道府県で比較してみる。ウィジェット内で計算式を設定する時にすんなりいかないので、ここはしっかりマスターしておきたい。

1. AVG関数で平均を求める

平均注文額を求める場合、２通りの方法がある。
(1)AVG関数を使って求める
(2)注文金額の合計 / 注文会員のユニーク数
特に(1)で平均を求める場合、何をもって平均するのか。
ここでは「会員ごと」であり、その会員ごとの平均を都道府県で比較することをゴールとしている。
その時に、全体の平均に対して、各都道府県は上回っているのか、下回っているのかも求めてみる。

1.1 ソート問題をカスタム列でクリアする

Sisenseはソートに弱い。このやり方が万能というのは、どうもないんじゃないかと最近思っている。
今回は都道府県を北～南に都道府県コード順に並べたいとする。
ウィジェット上で値のみで（区切り基準を使わず）単純な棒グラフや折れ線グラフにしたい場合は【Sisense Widget】棒グラフのソート順を設定する裏ワザで行った方法が有用なのだが、区切り基準を指定して積み上げグラフで表現したい場合などには破綻してしまう。
じゃあ、カスタム列を作ってこんなふうにしたら悩まずに済むんじゃないか。。。
01-北海道
02-青森県
・
・
47-沖縄県
これならテキストで普通にソートしてくれる（この表記が許されるというのが前提ではあるが）。
ではカスタム列をどうやって作るか。
まず、カスタム列の追加を行う。

都道府県ポイントデータの「都道府県コード」と「都道府県」を「-(ハイフン)」でつなげるためには以下のように数式を指定する。
・カラム名　 　#都道府県
・数式 　　　　RIGHT('0' + ToString([都道府県コード]),2) + '-' + [都道府県]

1.2 ウィジェットにAVG()で平均値を表示する

今回は、都道府県名を横に表示したいので「横棒グラフ（いわゆるBar Chart）」を作成する。
上で作成した「#都道府県」をカテゴリに設定し、値は次の数式を指定する。

AVG([会員番号],SUM([注文金額]))

ちょっと解説しよう。
チャートにおけるAVGの計算ではどの単位で平均するのかが重要だ。
Avg([注文金額])とすると、注文金額の平均なので、明細単位での平均となる。
今回の場合、顧客ごとの平均を求めたいわけだから、上記の数式で指定をすることになる。

ここで、明細テーブルの会員番号を指定する場合と会員テーブルの会員番号を指定する場合で結果が異なるので注意が必要だ。
前者の場合は注文実績のある会員の平均となる一方で、後者では全ての（注文実績のない会員も含めて）会員の平均となる。

整理すると以下がAVGの基本書式だ。

AVG(集計単位, 計算式)

このような「～ごとの集計」のことをSisenseではmulti-pass aggregationという。
multi-pass aggregationの書式を使用できる関数はAVGの他にMin(),Max()などがある。

1.3 平均値のラインを入れる

次に平均値の計算式を使用してさらにその平均を求めてみる。
具体的には、1.2では都道府県毎の平均値を求めたが、その平均を求めるという方法。

AVG([#都道府県],AVG([会員番号],SUM([注文金額])))

AVG(集計単位, 計算式)に当てはめただけなので、あえて説明は不要と思うので割愛する。
系列を「線」とし、色をオレンジで指定するとこうなる。

1.4 最大、最小のラインを入れる

1.3で入れた平均のラインと同様の方法で最大のラインを入れてみる。
まずは、先程作成した「平均」を複製する。

AVGをMAXに入れ替えるだけなので、数式はこれでいい。

MAX([#都道府県],AVG([会員番号],SUM([注文金額])))

数式を指定したら、系列を線にして色を緑で指定すると、以下のように会員当たりの注文金額が最も多い佐賀県と同じ高さに緑のラインが引かれている。

MAXと同じ要領でMINを指定すると会員当たりの注文金額が最も少ない秋田県と同じ高さに緑のラインが引かれる。

2. 全体平均を求める

現在のウィジェット内の平均ライン（オレンジ）は都道府県毎の平均を平均したものなので、全体平均ではない。
では都道府県を考慮せずに単純な全国平均を求めるにはどうしたらよいか。

2.1 AVGにALL()を追加してみる

単純な理屈からすると、今回のAVG()関数に【Sisense Formula】ALL()とStar(☆)の合わせワザで構成比を表示するで学んだALL()を追加してあげるとうまくいくんじゃないかと思う。では早速やってみるね。

ここで設定した数式は

(AVG(AVG([会員番号],SUM([注文金額]))),ALL([#都道府県]))

結果は

ありゃ、AVG([会員番号],SUM([注文金額]))と変わらない。
じゃあ、他のパターンで

(AVG([会員番号],SUM([注文金額])),ALL([#都道府県])) ・・・上の結果と同じ
(AVG([#都道府県],AVG([会員番号],SUM([注文金額]))),ALL([#都道府県]))・・・AVG([#都道府県],AVG([会員番号],SUM([注文金額])))と同じ

うーん...うまくいかない。

2.2 AVGを使わずに数式で平均を求める

冒頭で平均を求める方法は2種類あると書いた。
今度は平均を求める2つめの方法

(2)注文金額の合計 / 注文会員のユニーク数

を用いて計算する。
今回の例では、都道府県全体（都道府県を無視してと言ってもいい）の平均を求めるから、ALL()を用いて計算させる。

数式は以下のとおり

(SUM([注文金額]),ALL([#都道府県]))
/
(COUNT([会員番号]),ALL([#都道府県]))

解説するとAVG()関数に【Sisense Formula】ALL()とStar(☆)の合わせワザで構成比を表示するでご紹介したX軸を無視して合計を算出する方法を分子、分母で使用している。
結果はこうなる。

3. まとめ

〜ごとの平均の数式について説明してきた。
ちょっと納得いかないところもあるんだけどね。
そういうもんだと割り切って使おうね。

ではまた！