メイン コンテンツをスキップする

ネストされた集計関数

チャートの数式で利用される任意の項目名は、必ず 1 つの集計関数で囲む必要があることです。集約をネストする必要がある場合は、Aggr() を使用して 2 番目の集約レベルを追加できます。Aggr() には、引数として集計関数が含まれています。

関数では常に 1 つのレベルの集計

一般的なアプリには、次が含まれます。

  • データ内に 100 万レコード
  • ピボット テーブル内に 100 行
  • ゲージ、またはテキスト ボックス内の単一の KPI

影響の度合いは異なるにもかかわらず、3 つの数値すべてがデータすべてを表す場合があります。数値は異なる集計レベルです。

集計関数は多くのレコードの値を入力として使用し、すべてのレコードの要約として表示できる 1 つの単一の値に集約します。制限が 1 つあります。別の集計関数内で集計関数を使用することはできません。通常は、すべての項目参照を 1 つの集計関数でラップする必要があります。

次の数式が機能します。

  • Sum(Sales)
  • Sum(Sales)/Count(Order Number)

次の数式はネストされた集計であるため機能しません。

  • Count(Sum(Sales))

これに対する解決策は、Aggr() 関数の形で提供されます。その名前に反して、それは集約関数ではありません。これは「多対多」の関数であり、数学のマトリックスのようなものです。N レコードのあるテーブルを M レコードのあるテーブルに変換します。これは、値の配列を返します。また、1 つのメジャーと 1 つまたは複数の軸を持つ仮想ストレート テーブルと見なすこともできます。

ヒント メモ複数のレベルでネストされたチャート集計を作成する場合は、計算軸で Aggr() 関数を使用してください。

ネストされた集計に Aggr() を使用

Aggr() は、指定された軸上で計算された数式の値の配列を返します。たとえば、顧客別、地域別 sales の最大値です。高度な集計では、Aggr() 関数は別の集計関数で囲まれ、Aggr() の結果の配列をネストされる集計への入力として使用します。

これを使用すると、Aggr() ステートメントは仮想テーブルを生成し、1 つの数式が 1 つ以上の軸によってグループ化されます。この仮想テーブルの結果は、外部集計関数によってさらに集計できます。

最大平均注文値の計算

チャートの数式で単純な Aggr() ステートメントを使用してみましょう。

全体的な指標を地域レベルで表示するだけでなく、さらに 2 つの複雑な数式も表示する必要があります。

  • 各地域内のマネージャーによる最大平均注文値。
  • その最大平均注文値を担当するマネージャー。

各地域の平均注文値は、標準の数式 Sum(Sales)/Count([Order Number]) を使用して計算できます。

アプリ内の Nested Aggregations シートには、Aggr() function というタイトルのテーブルがあります。

次の手順を実行します。

  1. 使用可能な Aggr() function テーブルを選択します。
    プロパティ パネルが開きます。
  2. [列を追加] をクリックして、[メジャー] を選択します。
  3. 3 記号をクリックします。
    数式エディタが開きます。
  4. 以下を入力します。 Sum(Sales)/Count([Order Number])
  5. [適用] をクリックします。

地域ごとの平均注文値を表示するテーブル。

Table showing average order value per region.

ヒント メモベスト プラクティスとして、データが適正に形式設定されていることを確認してください。この場合は各列で、計算を表すように [ラベル] を変更します。金額の値を含む列では、数値書式Money に、書式パターン$ #,##0;-$ #,##0 に変更します。

私たちの目標は、各地域の最大平均注文値を取得することです。Aggr() を使用して、マネージャーごとに各地域の平均注文値を取得し、それらの最大値を表示することを Qlik Sense に通知する必要があります。各地域のマネージャーごとの平均注文値を取得するには、Aggr() ステートメントに次の軸を含める必要があります。

Aggr(Sum(Sales)/Count([Order Number]), Region, Manager)

この数式により Qlik Sense は、次のような仮想テーブルを生成します。

マネージャーごとの、各地域の平均注文値を表示する Aggr() 関数の仮想テーブル。

table with AGGR function showing average order value for each region, per manager.

Qlik Sense がマネージャーごとに各地域の個々の平均注文値を計算するとき、これらの値の最大値を見つける必要があります。これを行うには、Aggr() 関数を Max() でラップします。

Max(Aggr(Sum(Sales)/Count([Order Number]), Manager, Region))

次の手順を実行します。

  1. [列を追加] をクリックして、[メジャー] を選択します。
  2. 3 記号をクリックします。
    数式エディタが開きます。
  3. 以下を入力します。 Max(Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
  4. [適用] をクリックします。

マネージャーごとの、地域、各地域の平均注文値、各地域の最大平均注文値を表示するテーブル。

Table showing region, average order value, and largest average order value for each region, per manager.

地域レベルで、すべてのマネージャーの最大平均注文値を確認できます。これは、2 つの複雑な数式の 1 つ目です。次の要件は、これらの大きな平均注文値を担当するマネージャーの名前を値自体の横に表示することです。

これを行うには、以前のように同じ Aggr() 関数を使用しますが、今回は FirstSortedValue() 関数を併用します。FirstSortedValue() 関数は、関数の 2 番目の部分で指定された特定の軸について、マネージャーを提供するように Qlik Sense に指示します。

FirstSortedValue(Manager,-Aggr(Sum(Sales)/Count(Order Number), Manager, Region))

ヒント メモ数式には小さな、しかし非常に重要な部分が 1 つあります。Aggr() 数式の前にマイナス記号があります。FirstSortedValue() 関数内で、データの配列のソート順を指定できます。この場合、マイナス記号は Qlik Sense に最大から最小へのソートを指示します。

次の手順を実行します。

  1. [列を追加] をクリックして、[メジャー] を選択します。
  2. シンボルを Expressionクリックします。
    数式エディタが開きます。
  3. 以下を入力します。 FirstSortedValue(Manager,-Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
  4. [適用] をクリックします。

地域、各地域の平均注文値、各地域の最大平均注文値、その注文値を担当するマネージャーを表示するテーブル。

Table showing region, average order value, largest average order value for each region, and manager responsible for that order value.

詳細については、Aggr を参照してください

詳細については、FirstSortedValue を参照してください