Aggr関数の構造化パラメータとは？Aggr関数の知られざる機能のご紹介
以前、『Aggr関数再入門』という記事でAggr関数の使い方について解説いたしましたが、今回はさらにもう一歩踏み込んだAggr関数の応用的な使い方について解説いたします。
Aggr関数について詳しく知りたい方、ヘルプに掲載されている構造化パラメータ（Structured Parameter）について知りたい方、Aggr関数で意図した結果が得られないという方は、ぜひご一読ください。
Aggr関数の構文
QlikViewを古くからお使いの方は、ヘルプでAggrと検索したことが一度はあるかと思います。実は最近のバージョンでAggr関数の構文が拡張されたことはご存知でしょうか。
QlikView 11のヘルプでは、Aggr関数の構文は以下のように書かれています。
以下はQlikViewリファレンスマニュアルからの引用です。
aggr ( [ distinct | nodistinct ][{set_expression}]expression {, dimension} )
最近のバージョンであるQlikView 12やQlik Senseのヘルプでは、Aggr関数の構文は以下のように書かれています。
以下はQlikViewヘルプからの引用です。
※最近のバージョンではオンラインヘルプになりました。
Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})
新旧のバージョンで構文を比べてみてください。
最後の部分がバージョン11では「dimension」、バージョン12では「StructuredParameter」となっています。
“Dimension”というのは軸項目のことです。これに対して”Structured Parameter”は構造化パラメータと呼ばれる指定です。
では、軸項目と構造化パラメータで何が違うかというと、一番の違いは構造化パラメータでは単純な項目の指定に加えて、”値の並び順を制御できる”という点です。
つまり、バージョン11以前は単純な軸項目の指定のみが可能でしたが、バージョン12以降では単純な軸項目の指定に加えて、値の並び順を制御できるようになったということです。
ここまでの内容をまとめておきましょう。
Aggr関数は指定した軸項目ごとに値を集計する関数ですが、QlikView 11以前と12以降で以下の違いがあります。
QlikView 11以前：単純な軸項目ごとに値を集計する。軸項目（Dimension）の指定
QlikView 12以降：単純な軸項目ごとに値を集計するのに加えて、値の並び順を制御できる。構造化パラメータ（Structured Parameter）の指定
Aggr関数での値の並び順とは
構造化パラメータで値の並び順を制御できることがわかったところで、Aggr関数において値の並び順がどんな意味を持つのか順を追って説明していきます。まず、Aggr関数再入門の記事で説明したように、Aggr関数を使用すると内部的に仮想のテーブルが作成されます。
たとえば、以下のような指定があったとします。
Aggr(Sum(実績), 年)このとき内部的に、以下のような仮想テーブルが作成されています。
| 年 | 実績 |
|---|---|
| 2017 | 200 |
| 2016 | 100 |
| 2018 | 150 |
つまり上の仮想テーブルであれば、データが取り込まれた順がたまたま2017→2016→2018の順であったということになります。
だからなにが困るのかという点は追々説明してまいりますので、まずはこのような動作であるという点を押さえておいてください。
では、QlikViewの画面上で動作を確かめてみましょう。実際に試したい方は以下の手順にそって操作してみてください。
まず以下のような簡単なサンプルデータを作成します。
年月のデータですがあえて1ヶ所、月の順序を逆にしています。2015年3月と2月を3月→2月の順にしています。
LOAD * INLINE [
年, 月, 実績
2015, 1, 2
2015, 3, 5
2015, 2, 4
2015, 4, 5
2015, 5, 5
2015, 6, 5
2015, 7, 3
2015, 8, 2
2015, 9, 6
2015, 10, 6
2015, 11, 3
2015, 12, 5
2016, 1, 3
2016, 2, 4
2016, 3, 1
2016, 4, 4
2016, 5, 6
2016, 6, 8
2016, 7, 4
2016, 8, 7
2016, 9, 8
2016, 10, 5
2016, 11, 7
2016, 12, 5
2017, 1, 4
2017, 2, 3
2017, 3, 6
2017, 4, 4
2017, 5, 2
2017, 6, 5
2017, 7, 4
2017, 8, 6
2017, 9, 8
2017, 10, 5
2017, 11, 6
2017, 12, 6
];
リロードを実行したら、シートに月のリストボックスを追加してください。
リストボックスでは月が1月から順番に表示されます。これはリストボックス側で並べ替えがおこなわれているためです。設定を見てみましょう。リストボックスのプロパティで［ソート］タブを開きます。
［数値］にチェックが入っています。これにより、リストボックスでは1月から順に表示されます。設定を変更してみましょう。
［数値］のチェックを外して［ロード順］にチェックを入れ、［OK］ボタンをクリックしてください。
月の並び順が3月→2月の順になりました。これはデータを取り込んだ順です。動作が確認できたら、設定を元に戻しておいてください。
リストボックスのプロパティで［ソート］タブを開き、［ロード順］のチェックを外して［数値］にチェックを入れます。
それでは、今後はストレートテーブルを追加してAggr関数の動作を確認してみましょう。
軸と数式を以下のように設定します。ここではRowNo関数を使用して年月ごとに行番号を表示してみます。
軸：年と月
数式：RowNo()
年月ごとに行番号を表示できました。
このストレートテーブルに以下の数式を追加して、Aggr関数の動作を確認してみてください。
Aggr(RowNo(), 年, 月)※この数式はAggr関数の動作を確認するためのものです。実用性はありません。
年月ごとに行番号を表示するという数式ですので、前の数式とおなじ結果になりそうな気がします。
［OK］ボタンをクリックして表示を確認してみてください。
「RowNo()」と「Aggr(RowNo(), 年, 月)」で値の並び順が違っています。Aggr関数の方はデータを取り込んだ順に表示されていますね。では、ここまでの内容をまとめておきましょう。
Aggr関数以外の関数（例ではRowNo関数）は、チャート側で指定されたソート順に処理をおこなう。
Aggr関数は、チャート側で指定されたソート順に関係なくデータを取り込んだ順に処理をおこなう。
構造化パラメータの指定方法
それでは構造化パラメータを使用して、Aggr関数の値の並び順を変更してみましょう。あらためて構造化パラメータの指定方法を確認しておきます。構造化パラメータはAggr関数の軸項目の部分に、以下の形で指定します。
(軸項目名, (ソートタイプ, 昇順降順))ソートタイプは並べ替えの基準が文字なのか数値なのか、並べ替えの種類を設定するものです。先ほどリストボックスのプロパティの［ソート］タブで設定した内容に相当します。
昇順降順については、昇順の場合は「ASCENDING」を降順の場合は「DESCENDING」を指定します。
| ソートタイプ | ソートタイプの意味 | 昇順の指定 | 降順の指定 |
|---|---|---|---|
| NUMERIC | 数値 | ASCENDING | DESCENDING、REVERSE |
| TEXT | 文字 | ASCENDING、A2Z | DESCENDING、REVERSE、Z2A |
| FREQUENCY | レコード数 | ASCENDING | DESCENDING、REVERSE |
| LOAD_ORDER | ロード順 | ASCENDING、ORIGINAL | DESCENDING、REVERSE |
それでは、構造化パラメータを指定してみましょう。
今回は月を数値の昇順で並べ替えたいため、構造化パラメータの指定は以下のようになります。
※昇順降順の指定を省略したときの動作はヘルプには書かれていませんが、こちらで確認したところ昇順になりました。
(月, NUMERIC)Aggr関数の全文は以下のようになります。
Aggr(RowNo(), 年, (月, NUMERIC))
値の並び順が月の昇順になりました。
降順の指定も試してみましょう。昇順降順を指定する場合、構造化パラメータをさらに括弧で囲んで以下のように指定します。
(月, (NUMERIC, DESCENDING)))Aggr関数の全文は以下のようになります。
Aggr(RowNo(), 年, (月, (NUMERIC, DESCENDING)))
値の並び順が月の降順になりました。
ここまでで構造化パラメータの指定方法をご覧いただきましたが、お分かりいただけましたでしょうか。
括弧の指定が重なるため、はじめのうちは記述ミスをしてしまうかもしれませんが、構造化パラメータで値の並び順を制御できるということはお分かりいただけたと思います。
実践的な例
ここまでで、構造化パラメータの概要と指定方法を説明いたしました。しかし、実際どのようなときに役に立つのかは、なかなか想像がつかないと思います。
実際、構造化パラメータが必須となる機会はそれほど多くないのですが、いくつか実践的な例をご紹介します。
前述のとおり、構造化パラメータは値の並び順を制御するものです。つまり、値の並び順によって結果が変わるような場合に必要になってきます。
値の並び順によって結果が変わる演算とは、たとえば累計や移動平均などのことです。関連する関数で言えばRangeSum関数、RangeAvg関数、Above関数などが挙げられます。
累計値の例
ここでは以下のチャートについて考えてみます。このチャートではRangeSum関数とAbove関数を使用して累計値を表示しています。
累計値は以下の数式でもとめています。
RangeSum(Above(Sum(実績), 0, 12))Above関数はチャートの並び順に従って、上の行にある値を取得する関数です。RangeSum関数と組み合わせることで累計値を計算できます。
しかし、Above関数はチャートの並び順に従って、上の行にある値を取得する関数ですから、並び順が変わると結果が変わってきます。
たとえば、月を降順で表示すると12月から1月に向かって値が累計されていきます。
では、チャートの並び順を変えても常に1月から12月に向かって値を累計するにはどうしたらいいでしょうか。
このような場合に、Aggr関数と構造化パラメータが使用できます。
Aggr(RangeSum(Above(Sum(実績), 0, 12)), 年, (月, NUMERIC))
構造化パラメータにより、月は昇順に並べ替えることが定義されますので、チャートの並び順に関わらず常に1月から12月に向かって値が累計されていきます。
構造化パラメータを使わずに、Aggr関数のみ指定した場合の画面も掲載しておきます。
Aggr関数で集計基準は指定できるのですが、繰り返しお伝えしているとおり並び順がデータを取り込んだ順になるため、累計の順序もデータを取り込んだ順になります。
折れ線グラフの例
最後に折れ線グラフの例をご紹介します。この折れ線グラフではRangeAvg関数を使用して3ヶ月移動平均を表示しています。
この場合も、Aggr関数と構造化パラメータの指定が必要になります。なぜならば、折れ線グラフでは、1つ目の軸項目がX軸となり2つ目の軸項目ごとに折れ線が描画されるからです。
つまり、上図のように年ごとの比較をしたい場合、月を第1軸に年を第2軸に指定する必要があります。
そのため、もし単純にRangeAvg関数を使うと月ごと年ごとの値が計算されます。この例で言えば1月の中で2015年、2016年、2017年の3年間分の平均値が表示されます。
軸項目は月、年の順に設定し、集計は年、月の順におこなう必要があるためAggr関数の指定が必要になる訳です。
以前のバージョンでは、構造化パラメータが使用できなかったため、上のような表やグラフを作成したかった場合、ロードスクリプト側で事前にデータを並べ替えておく必要がありました。
構造化パラメータを使用することで、チャート側の指定だけでやりたいことが実現できるようになりました。
最後に…
今回はAggr関数と構造化パラメータについてご覧いただきましたが、いかがだったでしょうか。構造化パラメータは、もともと目に見えない部分を制御する機能ですのでなかなか難しかったと思います。
Aggr関数の結果が思いどおりにいかないときや、累計や移動平均がうまく計算できないときは、引数に指定した軸項目のソート順を確認してみてください。構造化パラメータで解決できるかもしれません。
お疲れ様でした。
