ネストされた集計関数

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

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

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

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

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

次の数式が機能します。

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

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

• Count(Sum(Sales))

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

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

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

最大平均注文値の計算

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

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

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

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

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

次の手順を実行します。

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

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

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

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

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

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

次の手順を実行します。

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

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

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

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

次の手順を実行します。

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