はじめに
Qlik SenseではAggr関数という強力な関数が提供されています。このAggr関数を使うことにより、指定した軸と数式に基づいて仮想的なテーブルを作成し、さらにこの仮想的なテーブルに対して集計処理を行って結果を出力する、といった形で多段階の集計処理を行うことができます。これにより、集計処理の柔軟性が高まって、かつ非常に高度な集計処理も行えるようなり、Qlik Senseで実現できる集計処理の幅が広がります。このエントリではこのAggr関数を以下の3つの用法に分けてご説明します。
- Aggrの基本的な利用方法
- AggrのCOUNTIFとしての利用
- Aggrの数式軸での利用
事前の準備
このエントリでは、「10分で試すQLIK SENSEでデータ分析」で利用した以下の売上データを利用します。
データの取り込み方などの詳細については、「10分で試すQLIK SENSEでデータ分析」をご参照ください。
Aggrの基本的な利用
「商品カテゴリ」を軸としたテーブルで分析を行う中で、まずは基本的な用途としてどういった場合にAggr関数が必要となるかを見ていきたいと思います。
まず、「各商品カテゴリに含まれる商品毎の平均売上金額」を算出するには「商品カテゴリ」を軸に設定し、以下の数式を入力します。(平均ということで「Avg([販売価格])」としてしまわない点に注意が必要です。この場合にAvgとするとレコード(=伝票)毎の平均が算出されてしまいます。)
Sum([販売価格])/Count([商品名])
各カテゴリごとに売上の合計を商品数で割ることで、商品ごとの売上の平均を算出形になっています。
では次に、「各商品カテゴリに含まれる商品毎の最大平均売上金額」を表示するにはどうすれば良いでしょうか？一旦商品毎の平均を上記の形算出して、その上で最大値を求める多段階の計算ステップが必要で、一筋縄にはいかないように見えます。このような場合にAggr関数を使うことが出来ます。
Aggr(Sum([販売価格])/Count([商品名]), [商品カテゴリ], [商品名])
これは、[商品カテゴリ], [商品名]の2軸で「Sum([販売価格])/Count([商品コード])」を集計する数式となっています。この数式より一時的に以下の様な一時的な仮想テーブルが作成されることをイメージすれば分かりやすいかと思います。
その上で、上記の数式を以下の形でMax関数で全体を括って最大値を取得します。
Max(Aggr(Sum([販売価格])/Count([商品名]), [商品カテゴリ], [商品名]))
この数式をテーブルにメジャーとして追加した結果が以下となります。先ほどの仮想テーブルの中の平均売上金額の最大値が「商品カテゴリ」毎に取得・表示されています。
次に、ここで算出した商品毎の平均購入額が最大となる商品はどれかを明らかにするためにその商品名を表示する列を追加したいと思います。
それを実現するために、以下の形でFirstSortedValue関数を利用します。
FirstSortedValue([商品名], -Aggr(Sum([販売価格])/Count([商品名]), [商品カテゴリ], [商品名]))
上記数式では先ほどと同様、、Aggrを使って[商品カテゴリ], [商品名]の2軸で「Sum([販売価格])/Count([商品コード])」を算出しています。先頭に「- (マイナス)」が付いているのは、その結果返される数値を降順でソートするように指示するためです。FirstSortedValueにより、この降順でソートされた結果の先頭の値(=最大値)の[商品名]を返しています。結果は以下の様になります。
AggrのCOUNTIFとしての利用
次に、集計した値が指定した値を超えたレコードのみをカウントする、COUNTIFの用法としてのAggrの使い方をご説明します。
ここでは、先ほどと同様のAggrの数式を利用して「平均購入金額が\10,000,000を超える商品の数」を商品カテゴリごとにカウントしたいと思います。式は以下となります。
Sum(If(Aggr(Sum([販売価格])/Count([商品名]), [商品カテゴリ], [商品名])>10000000, 1, 0))
まず、If(Aggr・・により、平均売上金額が10,000,000以上の商品には1、それ以外の商品には０のフラグを付与しています。この数式より一時的に以下の様なイメージのテーブルが作成されます。
そして、上記テーブルの一番右側に追加されたフラグをSum()で商品カテゴリ毎に集計する形となります。新たなテーブルを作成して上記数式をメジャーとして追加した結果は以下となります。
Aggrの数式軸での利用
「顧客の購入回数」毎に軸を分けて、それぞれの購入回数ごとに「購入金額合計」を集計する棒グラフを作成したいと思います。そのためにはまず「顧客毎の購入回数」を集計し、その購入回数を軸として顧客をグループ化して購入金額合計を算出する必要があります。
棒グラフを作成して以下の数式を軸に追加します。
=Aggr(Count(Distinct [伝票番号]), [顧客コード])
そして以下をメジャーに追加します。
Sum([販売価格])
そしてメジャーの「数値でソート」で昇順のソート設定を行うと、以下の形で「顧客の購入回数毎の購入金額合計」のチャートを作成することが出来ます。
まとめ
以上、3つの用法に分けてAggr関数の利用方法をご説明しました。Aggr関数を使うことで、ここでご説明したような形で高度な集計処理も行えるようなり、Qlik Senseで実現できる集計処理の幅を広げることが出来ます。