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



## はじめに

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

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


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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/5f03bc15-6e98-f3f0-2d5d-49af85384e00.png)








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





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




以下は初期状態から素直に使用できるフィールドを追加した状態です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/97e9ab69-9b7b-be12-23fc-831aa8172906.png)
ここから、足りないフィールドや要素を作成していきます。







## 計算フィールド

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

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

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

このうち、入力値を使用した計算、スイッチ用の計算フィールドに関しては本項では扱わず、「パラメータとコントロール」の項へ纏めます。
(操作用のパラメータ,コントロールを先に作成する必要があるため)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/9c688fb7-54a3-78f5-af41-10789790d083.png)

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



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

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

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



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


<br>

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

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

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

<br>
<br>
<br>


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

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

- PRE_FILTER － 事前フィルター計算は、データセットフィルターの前に計算されます。
- PRE_AGG － 事前集計計算は、集計と上位および下位の N フィルターをビジュアルに適用する前に計算されます。
- POST_AGG_FILTER － (デフォルト) テーブルの計算は、ビジュアルが表示されるときに処理されます。
[ユーザーガイド](https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/sumOver-function.html)より引用


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

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

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



<br>
<br>

尚、ビジュアル上で集計を「最小」とする必要があります。
比率自体は元々の1行ごとに存在する形となるため、
「合計」してしまうと、1商品の構成比%が存在する行数分加算されてしまいます。
(平たく言うと、100%を超えてしまいます。)
このため、「最小」の1行だけを取得することで、正しい値を表示させる必要があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/3268129d-34d8-7d78-6583-a76c32380843.png)








#### 構成比-カテゴリ(金額ベース)
同じように今度は、カテゴリごとの構成比を作成します。
同じカテゴリの部分は、同じ数値が並ぶ形です。

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


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

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






## パラメータとコントロール
#### 入力値による計算
続いては黄色の枠で示した「平均的な売上(指定日数間)」について作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/7b8d0a4a-0512-98d9-f6b5-ab259ceadf12.png)












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



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

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

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



まずは変数=パラメータを作成します。
型や初期値はパラメータ側により決定されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/ad3ed2e3-cc7e-181b-26b6-3c4ac0b47721.png)
入力値を使用して計算する場合、初期値の設定が必須となります。
今回は1週間分の意味で7とします。

作成したパラメータに紐づけて、値の入力場所であるコントロールを作成していきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/66f56d1a-b4a3-d8e4-e023-50d53a1eaf35.png)


















コントロールのスタイルは[ユーザーガイド](https://docs.aws.amazon.com/ja_jp/quicksight/latest/user/parameters-controls.html)に一覧があります。
今回は入力値を利用する為、テキストフィールドとします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/6fb480d2-012b-1321-94e3-a122b3538685.png)
ここまでで入力値計算のパラメータ・コントロールにの作成に関しては一通り終了です。 






<br>
<br>
<br>
<br>

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

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

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

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

余談ですが、この状態から以下のようにパラメータを編集し、デフォルト値を削除すると、初期値が空欄でありがならも計算に使用することが出来ます。(※動作の保証をするものではありません)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/6dfc1b8b-77cc-eedd-2849-aef92674af61.png)


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


<br>
<br>



#### 表示するフィールドの切り替え
最後に、黄色の枠で示した「構成比」について、緑枠の「表示切替」から、金額ベースと数量ベースで表示を切り替えるように変更します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/fce8cfec-78a5-3793-e9f3-e6ef10b2ca28.png)


















先ほどと同様に、まずはパラメータを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/128051f9-4c62-adc2-48d3-cde7a4546da4.png)






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

作成したパラメータに紐づけて、コントロールを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/66f56d1a-b4a3-d8e4-e023-50d53a1eaf35.png)










「金額」と「数量」から選択するように設定します。
下のチェックボックスにもチェックし、常に一方のみが選択された状態になるようにします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/e3d0f627-d56f-403c-4da6-ff80800ffac7.png)










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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/b3a5ec5c-4728-83a4-895e-2a2c9af36f5d.png)

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

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

続いて、「構成比-カテゴリ」です。
```
ifelse(
    ${switch}='金額',{構成比-カテゴリ(金額)},
    ${switch}='数量',{構成比-カテゴリ(数量)},
    0
)
```

<br>
<br>

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

## 組み立てと調整
使用する部品は全て完成しました。
組み立てて体裁を整えます。
表の「フィールドウェル」に、以下のようにフィールドを設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/dd8b61ea-7d10-f1ae-2259-be7f89f28ffe.png)

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

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

【月：2020/01 , 指定の営業日数：10日 , 表示切替：数量　選択時】
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/9f384bd1-c22d-2b97-e05c-ab75cdb1dbcd.png)


【月：2019/01 , 指定の営業日数：未選択 , 表示切替：金額　選択時】
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2743306/d7331114-829b-577e-9d17-64424b7a7d10.png)



## おわりに

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