【初心者向け】QuickSight計算フィールドとパラメータ・コントロールの使用方法

はじめに

この記事ではAWSのデータ可視化サービスであるQuickSightについて、計算フィールドやパラメータ・コントロールの使用方法を纏めます。「なんとなく使い方は解ったけど、もう少し色々出来ないのかな？」という方の参考になれば幸いです。

主な内容は、入力値による計算と、表示される列の切り替えスイッチ、おまけとしてsumOver関数です。

＜前提条件＞
QuickSight バージョン：2022/10/01以降のもの
QuickSight エディション：Standard以上

完成形

今回は以下のような表を作成しながら、手順を追っていきたいと思います。
※使用しているデータはテスト用のサンプルデータです。

「月」は単純な月のフィルタです。
「指定の営業日数」は、入力値を使用して「平均的な売上(指定日数間)」の列を表示します。入力値が空欄の場合は、列自体も空欄表示とします。
計算で使用する際は初期値の設定が基本的には必須の為、今回の初期値は7としています。
「表示切替」は「構成比-商品」「構成比-カテゴリ」列を、金額ベースと数量ベースで切り替える為のスイッチです。

作業手順

1. 下記計算フィールドを作成(sumOver関数)
   1. 構成比-商品(金額ベース)
   2. 構成比-商品(数量ベース)
   3. 構成比-カテゴリ(金額ベース)
   4. 構成比-カテゴリ(数量ベース)
2. 下記で利用する為のパラメータ・コントロールを作成
   1. 指定の営業日数　(入力値による計算)
   2. 表示切替　(表示するフィールドの切り替え)
   3. 月　(月を切り替えるフィルタ)
3. 以下の計算フィールドを作成
   1. 入力値による計算
   2. 表示するフィールドの切り替え
4. 組み立てと調整

以下は初期状態から素直に使用できるフィールドを追加した状態です。

ここから、足りないフィールドや要素を作成していきます。

計算フィールド

今回作成の必要があるフィールドは下記の通りです。
・構成比-商品(金額ベース)
・構成比-商品(数量ベース)
・構成比-カテゴリ(金額ベース)
・構成比-カテゴリ(数量ベース)

入力値を使用した計算(本項では扱わず後述します)
・平均的な売上

スイッチ用(本項では扱わず後述します)
・構成比(商品別)
・構成比(カテゴリ別)

このうち、入力値を使用した計算、スイッチ用の計算フィールドに関しては本項では扱わず、「パラメータとコントロール」の項へ纏めます。
(操作用のパラメータ,コントロールを先に作成する必要があるため)

この為、本項で作成するのは黄色の枠で示した部分で使用する「構成比」部分のみとなります。表示上は2列ですが、切り替え用に以下4点を作成します。
・構成比-商品(金額ベース)
・構成比-商品(数量ベース)
・構成比-カテゴリ(金額ベース)
・構成比-カテゴリ(数量ベース)

構成比-商品(金額ベース)

構成比として必要なフィールド4点のうち、まずは「構成比-商品(金額ベース)」を作成します。
月の売上のうちの、各商品が占める割合でつまりは、
sum(選択月の商品ごとの売上)/sum(選択月の売上)です。

表自体の構造に依らない集計を行う為には、sum()内に集計粒度を設定します。
例えば今回では、表の構造に関係なく「売上月」での集計を行う必要がある為、
sum(売上金額,[売上月])となります。

ここから単純に考えれば、式は以下の形となります。
sum(売上金額)/sum(売上金額,[売上月])
しかし、上記の計算ではエラーとなってしまいます。
※月毎での売り上げを全ての行に出すような場合はsum(売上金額,[売上月])で問題ありません。

ここで使用するのがsumOver関数です。
sumOver関数はLACと呼ばれる関数の一種なのですが、まずはLACについて簡単に纏めます。

LAC(Level Aware Calculation)とは

ビジュアルの集計レベルに依存しない計算が可能な関数です。
例えば今回であれば、特に指定なく値を入れると「カテゴリ」と「商品」によって自動的に集計されてしまいます。
「商品」を表示したまま、「カテゴリでのみ」の集計結果を表示する……といった事が可能になるのがLACの特徴です。
日本語ではレベルアウェア計算と呼ばれます。

先ほどのsum内に集計粒度を追加する方法も、LACの一種です。
先ほどの場合はLAC-A関数と呼ばれ、集計関数(例えばsum,max,count等)内に、集計に使用するレベルを追記して利用します。

一方で今から使用するのは、LAC-W関数と呼ばれる関数です。
こちらは集計レベルの指定だけではなく、集計のタイミングも指定可能な関数です。
例としては、sumOver(),countOver(),maxOver()等があり、
フィルターをかける前の値で集計したい！というような場合に使用されます。

使用するsumOver関数の構文は下記の通りです。
sumOver(値,[集計レベルの指定] ,集計タイミングの指定)

集計タイミングについては、以下3種のいずれかを指定します。
指定がない場合はPOST_AGG_FILTERが適用されます。

• PRE_FILTER － 事前フィルター計算は、データセットフィルターの前に計算されます。
• PRE_AGG － 事前集計計算は、集計と上位および下位の N フィルターをビジュアルに適用する前に計算されます。
• POST_AGG_FILTER － (デフォルト) テーブルの計算は、ビジュアルが表示されるときに処理されます。
  ユーザーガイドより引用

今回の場合、POST_AGG_FILTERではエラーとなってしまう為、PRE_AGGを指定し、
sum(各月の商品ごとの売上)/sum(各月の売上)の形に整えます。

sumOver(売上金額,[商品名,売上月],PRE_AGG)/sumOver(売上金額,[売上月],PRE_AGG)

これで、「構成比-商品(金額ベース)」ができました。

尚、ビジュアル上で集計を「最小」とする必要があります。
比率自体は元々の1行ごとに存在する形となるため、
「合計」してしまうと、1商品の構成比%が存在する行数分加算されてしまいます。
(平たく言うと、100%を超えてしまいます。)
このため、「最小」の1行だけを取得することで、正しい値を表示させる必要があります。

構成比-カテゴリ(金額ベース)

同じように今度は、カテゴリごとの構成比を作成します。
同じカテゴリの部分は、同じ数値が並ぶ形です。

式に関しては、先ほどの構成比-商品(金額ベース)で使用した式のうち、「商品名」を「商品カテゴリ」へ変更するだけです。
sumOver(売上金額,[売上月,商品カテゴリ],PRE_AGG)/sumOver(売上金額,[売上月],PRE_AGG)
こちらについても、ビジュアル上での集計は「最小」とする必要があります。

ここまでで、作成する必要のある計算フィールドのうち金額ベースのものが完成しました。
・構成比-商品(金額ベース)
・構成比-商品(数量ベース)
・構成比-カテゴリ(金額ベース)
・構成比-カテゴリ(数量ベース)

数量ベースの式については、それぞれ売上数量を使用して同様に作成します。
繰り返しとなるため説明は割愛いたします。

パラメータとコントロール

入力値による計算

続いては黄色の枠で示した「平均的な売上(指定日数間)」について作成します。

出したいのは月の売上から割り出した「指定の営業日数」での売上金額です。(※決まった期間の売上ではなく、あくまで月の売上から算出する平均値)
つまり式は以下の様になります。
「売上金額」×「指定の営業日数」で指定した値/その月の総営業日数

さて、このように入力値を使用して計算したい場合に使用するのが、パラメータとコントロールです。簡単なイメージとしては以下の通りです。

• コントロール：値の入力窓口
• パラメータ：値の入れ物(変数)

QuickSightでは「コントロール」から入力した値を「パラメータ」として使用します。
これらを使用することで、入力値による計算や、表示を切り替えるスイッチなどが可能となります。今回は列の一部を切り替えますが、表示するビジュアル自体を切り替えることも可能です。
※こちらについてはQuickSightにおけるビジュアルの表示・非表示制御参照

まずは変数=パラメータを作成します。
型や初期値はパラメータ側により決定されます。

入力値を使用して計算する場合、初期値の設定が必須となります。
今回は1週間分の意味で7とします。

作成したパラメータに紐づけて、値の入力場所であるコントロールを作成していきます。

コントロールのスタイルはユーザーガイドに一覧があります。
今回は入力値を利用する為、テキストフィールドとします。

ここまでで入力値計算のパラメータ・コントロールにの作成に関しては一通り終了です。

続いて、計算フィールドを作成します。

金額*${days}/選択月の総日数

「選択月の総日数」に関しては、年中無休と仮定して、選択した月の日数を返す計算フィールドを別途作成しています。
${days}が、先ほど作成したパラメータを指します。
これだけでも問題ないのですが、「指定の営業日数」の欄の入力を空欄にすると、表そのものが表示されなくなる場合がある為、nullの処理を追記します。

ifelse(isnull(${days}), null, 売上金額*${days}/選択月の総日数 )

余談ですが、この状態から以下のようにパラメータを編集し、デフォルト値を削除すると、初期値が空欄でありがならも計算に使用することが出来ます。(※動作の保証をするものではありません)

入力値による計算「平均的な売上(指定日数間)」については以上です。

表示するフィールドの切り替え

最後に、黄色の枠で示した「構成比」について、緑枠の「表示切替」から、金額ベースと数量ベースで表示を切り替えるように変更します。

先ほどと同様に、まずはパラメータを作成します。

「金額」と「数値」から選択する為、データタイプは文字列です。
こちらも計算フィールドで使用する為、初期値を便宜上「金額」として設定しています。

作成したパラメータに紐づけて、コントロールを作成します。

「金額」と「数量」から選択するように設定します。
下のチェックボックスにもチェックし、常に一方のみが選択された状態になるようにします。

以上の手順で作成すると、このように選択肢を作成できます。

ここから受け取った「金額」あるいは「数量」を判別し、
構成比の表示を切り替える為の計算フィールドを作成します。

まずは、「構成比-商品」についてです。
${switch}の値を判別し、計算フィールドの項で作成した「構成比-商品(金額)」「構成比-商品(数量)」を返しています。

ifelse(
    ${switch}='金額',{構成比-商品(金額)},
    ${switch}='数量',{構成比-商品(数量)},
    0
)

続いて、「構成比-カテゴリ」です。

ifelse(
    ${switch}='金額',{構成比-カテゴリ(金額)},
    ${switch}='数量',{構成比-カテゴリ(数量)},
    0
)

手順は割愛しますが、この表自体は選択した月ごとのデータを表示させる為、もう一組、パラメータ・コントロールのセットを作成しています。
こちらはフィルタへ紐づけて、選択した月のデータを見られるようにしてあります。

組み立てと調整

使用する部品は全て完成しました。
組み立てて体裁を整えます。
表の「フィールドウェル」に、以下のようにフィールドを設定します。

先述の通り、「構成比-商品」と「構成比-カテゴリ」に関しては、集計方法を「最小」とする必要があります。

その上で、コントロールをシート側へ追加、%表示化、パラメータ使用のタイトルへの変更等、適宜調整を行い以下のような形になりました。これで完成です。

【月：2020/01 , 指定の営業日数：10日 , 表示切替：数量　選択時】

【月：2019/01 , 指定の営業日数：未選択 , 表示切替：金額　選択時】

おわりに

今回は表形式でしたが、他のビジュアルにも応用可能です。QuickSightは計算式が上手く通らないことが多々ある為、そちらについても今後纏めていければ思います。
ここまでご覧いただきありがとうございました！