Amazon QuickSightで集計式を条件分岐させたい時の作り方

データアナリティクス事業本部の武田です。 今日は、QuickSightの条件付き集計について説明します。一つの列の中でフラグによって集計式を分岐させたい場合の作り方です。

集計を条件分岐させて実現したいこと

「一つの列の中でフラグによって集計する式を分岐させたい」と言葉で書いても何をしたいのか分かりにくいので、例えばで説明します。

今回も、Tableauをインストールしたときについてくる「サンプルスーパーストア」のデータを使います。

このデータには、商品のオーダーデータが入っています。受注日、出荷日、商品のカテゴリー、商品名、売上、数量等です。商品のカテゴリーは、家電・家具・事務用品の３つです。

ここで、架空の設定を考えます。下記のように、カテゴリーによって配送のオプション料金がペナルティ料金が違うという設定です。

＜家電ルール＞

即日出荷できたら速達料金として、１個につき1,000円もらえる。

出荷までに6日以上かかってしまったら、1個1日につき500円のペナルティを払わなきゃいけない。

＜家具ルール＞

速達料金の設定はない

出荷までに6日以上かかってしまったら、1個1日につき100円のペナルティを払わなきゃいけない。

＜事務用品ルール＞

速達料金の設定はない

出荷が遅れてもペナルティはない。

上記ルールを適用すると、速達料金がもらえるのは青い背景のところ、ペナルティを払わなきゃいけないのは赤い文字のところになります。

ペナルティの計算は、下記の通り、カテゴリーごとに掛け算の計算式を切り替えたくなります。

これを実現するために、「カテゴリーごとに計算式を分岐して集計し、速達料金とペナルティ金額を表示したい」ということです。

下記の様に、カテゴリーごとの速達料金とペナルティ料金の一覧表を作るのが、今回のゴールです。

事前準備：家具用と家電用の計算フィールド

ペナルティがカテゴリーごとに異なるので、まずそれぞれのペナルティ金額の計算フィールドを用意しました。

ifelse文を試してみた→ifの結果に集計関数のsumは入れられなかった

まず頭の中に思い浮かんだのが、ifelse文です。条件分岐といえばifelseですから、事前準備で作った計算フィールドを切り替えてsumしたらいいのかなと考えました。

が、しかし・・・ifで分岐して返す結果にsumを入れると、下記の様にエラーメッセージがでて、保存できませんでした。

このエラーについてしばらく調べていたのですが、ifから返す値は「数値そのもの」だったら問題はなく、sumやcountといった集計関数の場合に発生するエラーでした。

sumifを試してみた→計算はできたけど、そうじゃない・・・

ifを使った関数として、他にsumifやcountifがあります。これを試してみました。

条件に該当する場合に集計するだけなので、条件に該当しない時は集計されません。elseがない状態なので、家具の場合はこうで、家電の場合はこうでという分岐ができません。

これだと、家具用の計算フィールド、家電用の計算フィールドと複数の計算フィールドが必要になります。

よって、同じ様に家電の計算フィールドも追加して、下記の様な一覧表ができました。

でもこれだと、ペナルティ金額の列がカテゴリーごとに分かれてしまっています。理想はペナルティ金額として一つの列にして、下記の様な状態にしたいので、sumifではやりたいことが実現できませんでした。

sumの中にifが書ける！→実現できた

結論：sumの中にifを書いて分岐させることができました。

計算式は下記の通りです。

sumの中にifを書いて、ペナルティと速達の計算フィールドを作り、下記の様な一覧表を作ることができました。

ふりかえり

sumの中にifが書けるのは盲点で、できた瞬間に「そうか！」となったので記事にしてみました。これができると、いろんな場面で表現力が増えそうです。