Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
はじめに
これまでの「10分で試すQlik Senseでデータ分析」などのチュートリアルでは主にSumを使ったシンプルな集計を扱ってきました。ここではSum,Total,Count,Distinct,Avgなどの集計関数やオプションを使って、様々な集計の要件に対応するための方法をより細かくご説明します。
軸を跨った合計の算出-Total
以下は基本的なSum関数の使い方となり、軸に設定した「商品カテゴリ」毎の売上を集計しています。
では、ここで「全体の売上に対する各商品カテゴリの売上構成比」を算出したい場合にはどうすれば良いでしょうか？この様な時に利用するのがTotal句となります。以下の形でSum()の中にTotalを利用すると、軸に設定されている「商品カテゴリ」を跨って全体の売上合計を出力します。
Sum(Total [販売価格])
そして、以下の形とすると、全体の売上に対する各商品カテゴリの売上構成比を算出することができます。
Sum([販売価格])/Sum(Total [販売価格])
また、以下の様に「商品カテゴリ」＞「商品名」という形で2軸となっている場合を見てみましょう。ここで先ほどのTotalを使うと、先ほど同様に全体の売上が集計されます。ただ、このような2軸で集計を行った場合、全体の売上ではなく「商品カテゴリの売上合計に対する、カテゴリに含まれる各商品の売上構成比」を算出したい場合があると思います。
そのような場合は以下の形でTotalの後に集計を行う単位の軸の名前を<>で括って指定します。そうすると以下の様な形で全体ではなく「商品カテゴリ」ごとの合計を計算して、各商品カテゴリの中での各商品の売上構成比を算出することができます。
Sum(Total<商品カテゴリ> [販売価格])
選択を無視した合計の算出 – SET分析の活用
次にSET分析を活用して、選択を無視した合計の算出を行う以下の例を見てみます。ここでは、SET分析の「{1}」を指定しており、現在の選択範囲を無視して常に全体のレコードセットを対象として集計を行います。(SET分析については「思い通りのチャート集計を実現–Qlik SenseのSET分析」のエントリをご参照ください。)
Sum({1} [販売価格])
そうすると、例えば上記の例では「都道府県」で「東京」のみの絞り込みを行っており、「Sum([販売価格])」にはその選択が適用されますが、「Sum({1} [販売価格])」には適用されないため、「商品カテゴリ毎の東京都での売上」を算出することができます。
同様に他の項目で絞り込みを行うと、軸に設定した「商品カテゴリ」単位で、全体のレコードに対する選択で絞り込まれたレコードの売上割合を出力することができます。
レコード数のカウント – Count, Count Distinct
レコード数のカウントはCount関数を使い、例えば以下の形で「伝票番号」の数をカウントして「商品カテゴリ」毎の取引数を集計することができます。
ただ、Count関数を利用するときには「延べ数」をカウントするのか、「ユニーク数」をカウントするかを意識することが必要な点に注意する必要があります。以下の例では、「Count([販売日付])」とすると延べ数をカウントしているので上記の「伝票番号」をカウントした値と同じになっていますが、「Count(Distinct [販売日付])」とすると重複が排除されてユニークな日付数がカウントされています。
ただここで注意が必要なのは、キーとなっている項目に対するCount関数の利用する場合です。例えば以下の様なデータモデルで「Count([商品コード])」で集計した場合、どの様な集計が行われると期待するでしょうか？「商品コード」がキーとして「商品マスタ」と「売上明細」の両テーブルに含まれているため、数式の定義があいまいになってしまっています。
このように集計の定義があいまいになってしまうため、このような形でキーとなっている項目をCount関数で利用する場合には、求めていない値が算出されないように常にDistinctを付けた数式で利用することが基本的には推奨されます。
Count(Distinct [商品コード])
平均値の算出 – Avg, Sum()/Count(), Aggr
平均値の算出にはAvg関数が用意されており、以下の形で利用します。
ここで注意が必要なのは「何の平均値を算出しているか？」で、このような形で売上明細テーブルの販売価格に対してAvg関数を利用すると「取引(=伝票番号)毎の平均値」を算出します。
ただ、多くの場合では取引単位ではなく、例えば月や商品単位などの平均値を算出するようなことを行います。そのような場合には以下の様な数式を利用します。この例では、商品カテゴリでグループ化して、商品毎の平均売上高を算出しています。
Sum([販売価格])/Count(Distinct [商品名])
また、商品毎といった形で1層での平均ではなく、商品と都道府県といった形で複数層での平均を算出する場合には、以下の形で文字列を結合することで可能です。
Sum([販売価格])/Count(Distinct [商品名] & '|' & [都道府県])
尚、以上の計算はAggr関数を利用して行うことも可能で、それぞれ以下の形となります。(Aggr関数は「Qlik SenseのAggrによる高度な集計処理」で詳細をご紹介しています。)
Avg(Aggr(Sum([販売価格]),[商品名])) Avg(Aggr(Sum([販売価格]),[商品名],[都道府県]))
まとめ
以上、Sum,Total,Count,Distinct,Avgなどを使った集計の方法をご説明しました。