Aggr関数再入門（Aggr関数の詳細と使用例）

今回はSET分析と並んで難しいと言われることが多い、Aggr関数について解説いたします。

Aggr関数は、QlikViewやQlik Senseの関数の一種なのですが、Sum関数やAvg関数などの集計関数とはすこし毛色の違う関数です。
そのため、使い慣れるまではイメージがわきにくく、SET分析とおなじように難しい機能の一つとして捉えられているようです。

この記事では、Aggr関数について裏側の動作から詳しく解説していきますので、いままでAggr関数を使ったことがないという方や、苦手意識を持っていた方は、ぜひご覧ください。SET分析ほど難しくありませんのでご安心ください。

Aggr関数とは

Aggr関数の語源は、Aggregate（アグリゲート）からきています。
Aggregateは集計する、集約するといった意味ですが、これはどういうことなのでしょうか。まずはマニュアルを見てみましょう。
以下は、Qlik Senseヘルプの該当箇所からの引用です。
なお、Aggr関数はQlikViewでもQlik Senseでもおなじように使用できます。以下はQlik Senseヘルプからの引用ですが、QlikViewヘルプにもおなじことが書かれています。
Aggr – チャート関数 Qlik Sense
Aggr QlikView

Aggr() は、指定された軸上で計算された数式の値の配列を返します。

重要なポイントが2点書かれています。
・Aggr関数は指定した軸項目ごとに値を集計します。
・Aggr関数は結果として複数の値を返します。（配列とあるのは、複数の値という意味です。）
Aggr関数は指定した軸項目ごとに値を集計し、その結果を複数（軸項目の値の数だけ）返すということですね。

どういうことか簡単な例を使って説明します。
まず、Aggr関数の基本的な構文は以下のようになります。
Aggr関数は”指定した軸項目ごとに値を集計”しますので、軸項目の名前と値を集計するための数式を指定します。

Aggr(数式, 軸項目名)

たとえば以下のように指定すると、地区名ごとに売上金額を集計し、その結果（の配列）を返します。

Aggr(Sum(売上金額), 地区名)

これは言ってみれば、下図のようなチャートを作成して値を集計しているのとおなじことです。

事実、Aggr関数を使用すると内部的に仮想のテーブルが作成されています。
以下はヘルプからの引用です。

Sum、Min、Avgなどの基本的な集計関数では数値が1つ返されるのに対し、Aggr()関数は、一時的な段階の結果セット（仮想テーブル）を作成することと比較することができ

Aggr関数はこのように、普段チャートに軸とメジャーを指定してやっていることを、一つの関数の中でおこないます。

では、なぜチャートでできることを関数でおこなう必要があるかというと、こちらもヘルプからの引用ですが以下をご覧ください。

高度な集計では、Aggr 関数の結果の配列をネストされる集計への入力として使用し、別の集計関数で Aggr 関数を囲みます。

Aggr関数を使用することで、集計関数をネスト（入れ子に）できるということです。
たとえば、集計（Sum）したものの最大値（Max）をもとめることができます。具体的には地区ごとに売上金額を集計して、その中から最大のものをもとめるといったことです。

Aggr(Sum(売上金額), 地区名)) → 1337, 1413, 334, 413, 631, 1824, 901, 1086
Max(Aggr(Sum(売上金額), 地区名)) → 1824

動作の確認

では簡単なサンプルデータを使って、Aggr関数の動作を検証してみましょう。
完成版のアプリも公開しておきますが、実際に操作した方が理解が深まると思いますので、ぜひ実際に操作してみてください。
aggr-function.zip

新しくアプリを作成します。
アプリの名前はここでは「aggr-function-1」としておきます。

今回は簡単なサンプルデータをロードスクリプトで作成するため、右側の「データロードエディタ」を選択してください。

以下のロードスクリプトを貼り付けてください。

Load RowNo() as 連番,
Mod(RowNo(), 3) as 軸,
Floor(Rand() * 100) as 数値,
1 as 定数
AutoGenerate 10;

このロードスクリプトでは、「AutoGenerate」を使用して10件のデータを作成しています。
RowNo関数やRand関数を使用して、連番の項目やランダムな値を作成しています。

ロードスクリプトを貼り付けたら、「データのロード」を実行してください。

データのロードが完了したら、アプリ概要を開いてシートを作成します。

シートの名前は任意で構いませんので、「新しいシート」のままEnterキーを押してください。

まず、作成したサンプルデータを確認してみましょう。
画面左側の「チャート」パネルから「テーブル」を、ドラッグ&ドロップしてください。

「項目」パネルから「連番」を、テーブルの上にドラッグ&ドロップしてください。

「連番」は軸として追加するため、「”連番を追加”」を選択します。

「連番」が追加できたら、おなじようにして「軸」と「定数」の項目もテーブルに追加してください。

「項目」パネルから「数値」を、テーブルの上にドラッグ&ドロップしてください。

「数値」はメジャーとして追加するため、「メジャーとして追加」から「Sum([数値])」を選択してください。

テーブルが作成できました。

作成したサンプルデータが、どのような値を持つか確認しておいてください。
連番：1から10までの連番の項目です。
軸：1、2、0、1、2、0…という繰り返しの値を持つ項目です。Mod関数で剰余（割り算の余り）をもとめると、こういった繰り返しの値が作れます。
定数：常に1という値を持つ項目です。
数値：ランダムな数値です。Rand関数を使用してランダムな値を生成しているため、毎回結果は異なります。上の画面キャプチャと結果が違っていても気にしないでください。

値の確認用にもう一つテーブルを追加します。
いま作成したテーブルをコピーして、貼り付けてください。

テーブルを貼り付けたら、「連番」と「定数」の項目を削除します。

「軸」ごとの集計値を確認しておいてください。

「軸」は0、1、2の3つの値があり、「Sum([数値])」の値はそれぞれ236、166、243です。（この値は人によって異なります。）

以上で事前準備は完了です。

では、Aggr関数の動作を確認していきます。
「チャート」パネルから「テキストと画像」を追加してください。

画面右側の「プロパティ」パネルで「メジャーを追加」を選択します。

以下の数式を指定してください。

Aggr(Sum(数値), 軸)

この例では「軸」ごとに「数値」が集計されるため、236、166、243という3つの値が返されるはずです。
しかし、そのままでは結果はなにも表示されません。

これがなぜかと言うと、Qlik SenseやQlikViewでは、一つの値しか受け取れないところ（今回のテキストと画像など）に、複数の値を渡すと結果はなにも表示されないためです。

もう少し厳密に言うと、一つの値しか受け取れないところでは自動的にOnly関数が適用されます。
今回の指定は、以下の指定とおなじ意味になります。

Only(Aggr(Sum(数値), 軸))

Only関数では値が複数ある場合、結果は空（Null値）になりますので、今回のように結果はなにも表示されません。

では逆に、結果が一つであれば表示されることを確認してみます。
今回のサンプルデータには「定数」という、常に値が「1」である項目を用意してあります。
Aggr関数の軸項目を「定数」の項目に書き換えてください。

Aggr(Sum(数値), 定数)

「定数」の値は常に「1」であるため、結果として総合計である「645」が表示されます。
総合計を表示するのであれば、単純に「Sum(数値)」と指定すればいいため、この指定に実用性はありません。
しかし、値が一つであれば表示されるということは、ご理解いただけたかと思います。

結果を確認したら、軸項目を「軸」の項目に戻しておいてください。

それでは、他の指定も試してみましょう。
Aggr関数の結果を確認したい場合、Concat関数が便利です。
Aggr関数の外側に、以下のようにConcat関数の指定を加えてください。

Concat(Aggr(Sum(数値), 軸), ', ')

Concat関数は複数の値を連結するものです。今回は3つの値を,（カンマ）で区切って表示できました。
このようにConcat関数を使用すると、Aggr関数の動作がよく分かります。

では、Concat関数の指定をSumやAvgなどの一般的な集計関数に置き換えてみましょう。
まず実用性はまったくありませんが、Sum関数を指定してみてください。

Sum(Aggr(Sum(数値), 軸))

これは合計したものをさらに合計しますので、総合計がもとめられます。「Sum(数値)」と指定するのとおなじですので、前述のとおり実用性はありません。

では、Avg関数、Min関数、Max関数なども試してみてください。

Avg(Aggr(Sum(数値), 軸))

Min(Aggr(Sum(数値), 軸))

Max(Aggr(Sum(数値), 軸))

ここまでで、Aggr関数の動作について詳しく見てきましたが、いかがだったでしょうか。
途中でご覧いただいたように、つまずいたときはConcat関数で値を確認してみるのがお勧めです。

Concat(Aggr関数, 区切り文字)

区切り文字は,（カンマ）などが分かりやすいと思いますが、Chr(13)で改行コードを指定するのもいいでしょう。

Concat(Aggr関数, ', ') カンマ
Concat(Aggr関数, Chr(13)) 改行コード

使用例

ここからは、より実践的な使用例について見ていきましょう。
使用例については、開発元が作成したテクニカルブリーフが参考になります。（英語）
QlikView Technical Brief – AGGR.docx | Qlik Community

こちらは完成版のアプリでご確認ください。
以下のZipファイルをダウンロードしてください。
Zipファイルを解凍し、「aggr-function-2.qvf」をQlik Senseで開いてください。
aggr-function.zip

使用例1 集計関数のネスト

1つ目のシートでは、右側のテーブルで4つAggr関数を使用しています。

それでは、各Aggr関数の指定について簡単に解説していきます。

Max(Aggr(Count([売上金額]), 分類名１, 分類名２))

先ほどもでてきたMax関数の例です。商品の分類ごとに最大値をもとめています。たとえば「AV機器」の中では「オーディオ」の「2328」が最大値、「キッチン家電」の中では「コーヒーメーカー」の「1847」が最大値です。

Sum(If((Aggr(Count([売上金額]), 分類名１, 分類名２)) >= 1000, 1, 0))

If関数により、Aggr関数の結果が1000以上だったら「1」、そうでなければ「0」を設定し、Sum関数で集計しています。
このように、ある条件を満たすものの件数を数えたいとき、0と1をセットしてSum関数で集計するというのは、よく使用する小技ですので覚えておくとよいでしょう。

FirstSortedValue(分類名２, -Aggr(Count([売上金額]), 分類名１, 分類名２))

FirstSortedValue関数は、任意の基準で並べ替えたときの最初の値を返します。
さきほどのMax関数の例では最大値そのものを表示していましたが、最大値をもつ商品を表示したい場合に使用します。
たとえば「AV機器」の中では「オーディオ」の「2328」が最大値、「キッチン家電」の中では「コーヒーメーカー」の「1847」が最大値ですが、最大値そのもの（2328などの数値）ではなく、「オーディオ」「コーヒーメーカー」などの商品名の方を表示します。

こちらも、Aggr関数の部分を省略した以下の形で考えてみましょう。

FirstSortedValue(分類名２, -Aggr関数)

Aggr関数の前に-（マイナス）を着けて順位を逆転させています。LastSortedValueといった関数はないため、最大値を基準にしたい場合は-（マイナス）を着けて順位を逆転させます。
これにより、Aggr関数の結果が最大のときの「分類名２」の値をもとめています。

Concat(If(Aggr(Rank(Count([売上金額])), 分類名１, 分類名２) <= 3, 分類名２), ', ')

Concat関数やRank関数を組み合わせることで、順位が3位以内（上位3位）の商品のリストを作っています。

まず、Aggr関数の中の指定を見てみましょう。

Aggr(Rank(Count([売上金額])), 分類名１, 分類名２)

Rank関数により順位を作成しています。

Aggr関数の部分を省略すると、以下のようになります。

Concat(If(Aggr関数 <= 3, 分類名２), ', ')

Aggr関数の結果（順位）が3以下だったら、「分類名２」を返します。
されにこれをConcat関数で連結することで、3位以内のもののリストを作成しています。

ほかにもいくつか使用例を入れてありますので、2つ目以降のシートも見てみてください。

使用例2 集計関数のネスト（KPI編）

2つ目のシートでは、集計関数のネストを「KPI」や「テキストと画像」で使用しています。

Aggr関数の中身自体は1つ目のシートとほぼ同様ですが、KPIのようにデータ全体から1つの値を計算したい場合、Aggr関数を使う機会が多くなります。
1つの数式の中で計算を完結する必要があるため、必然的にAggr関数を使う機会も多くなるということですね。

使用例3 計算軸の作成

3つ目のシートでは、度数分布を作成しています。

このように、チャートの軸（X軸）を計算によって作成するというのも、Aggr関数の代表的な使用例です。
4つ目のシートは、度数分布でX軸を5単位にクラス分けした例です。

使用例4 プロパティの設定

5つ目のシートでは、基準線の計算にAggr関数を使用しています。

単純にAvg関数で平均値をもとめると、明細の平均値となるため基準線としては使えません。ここでは地区ごとに集計したものの平均値をもとめるために、Aggr関数を使用しています。
考え方としてはKPIのときとおなじです。1つの数式の中で計算を完結するためにAggr関数を使用しています。

なお、基準線は「拡張機能」→「基準線」→「基準線を追加」から設定できます。

注意事項

それでは、最後に注意事項をまとめて終わりにしたいと思います。

軸項目に数式は指定できない

Aggr関数の軸項目名には単純な項目名を指定する必要があります。数式は指定できません。

Aggr(数式, 軸項目名) ○
Aggr(数式, 軸を作成するための数式) ×

以下はヘルプからの引用です。

制限事項：
Aggr()関数の各軸は、単一の項目でなければならず、数式（計算軸）にすることはできません。

軸項目を複数指定することは可能です。
たとえば、以下のように指定すると、地区名ごと、都道府県名ごとに売上金額を集計します。

Aggr(Sum(売上金額), 地区名, 都道府県名)

パフォーマンスに注意する

前述のとおり、Aggr関数を使用すると内部的に仮想のテーブルが作成されるため、データ量が多い場合などはパフォーマンスが低下する可能性があります。
データ量が多い場合などは、以下のことを検討してください。

単純な計算に置き換えられないか検討する

たとえば、平均値は集計値を件数で割ればもとめられる場合があります。以下の3つの数式はすべておなじ結果になる可能性があります。

Avg(Aggr(Sum(売上金額), 都道府県名))
Sum(売上金額) / Count(DISTINCT 都道府県名)
Sum(売上金額) / 47

必ずしもおなじ結果になる訳ではないという点に注意してください。この例では売上を1件も上げていない都道府県があった場合、結果が違ってくる可能性があります。

ロードスクリプトで、あらかじめ計算しておくことを検討する

パフォーマンスの低下が著しいようであれば、ロードスクリプト側であらかじめ事前集計しておくことも検討してください。
その際は、Residentによるテーブルの再読み込みや、Group Byによるグループ化をうまく使ってください。

売上:
Load 商品名,
得意先番号,
得意先名,
売上金額,
:
From ...;

得意先別売上集計:
Load Sum(売上金額) as 得意先別売上集計, //Sum関数による集計
得意先番号
Resident 売上 //Residentによるテーブルの再読み込み
Group By 得意先番号; //Group Byによるグループ化

新バージョンでは構文が拡張されている

Qlik Sense 2.2、QlikView 12から構文が拡張され、Aggr関数の結果の並び順を制御できるようになりました。
わたしは、まだ新しい構文を使う必要性に迫られたことがないため、利点を実感できていないのですが、構文が拡張されたことは認識しておくとよいでしょう。
なお、QlikViewの方は、まだヘルプに反映されていないので注意してください。
詳細は、コミュニティの以下のブログ記事が参考になります。（英語）
Qlik Design Blog : The sortable Aggr function i... | Qlik Community

最後に...

今回はAggr関数についてご覧いただきましたが、いかがだったでしょうか。
慣れるまでは難しいかもしれませんが、Aggr関数の動作はお分かりいただけたかと思います。
そこから先は、ヘルプなどから例文を探してきてアレンジする作業になりますので、記事の中で複雑な数式も出てきたと思いますが、必要以上に難しく考えることなく、まずはコピペで活用するところからはじめてみてください。

式が複雑になる場合は、一旦式を分解してConcat関数で動作を確認しながら進めていけばいい訳ですし、また、式があまりにも複雑でメンテナンス性が著しく低下するような場合は、ロードスクリプト側で事前集計しておくという方法もあります。
ぜひ難しくとらえずに、便利な機能の一つとして活用してみてください。

お疲れ様でした。