Quicksightで複数月の平均値を合計する方法をご教示いただきたいです。
Quicksightで棒グラフやピボットテーブルを作成しています。
現状:
月単位でグラフを表示すると、各月の平均値が正しく表示されます。
しかし、コントロールフィルターで複数月（例：4月と5月）を選択すると、2か月分の単純な平均値 (4月の平均 + 5月の平均) / 2 が表示されてしまいます。
やりたいこと:
複数月を選択した場合、棒グラフで各月の平均値を合計した値 (4月の平均 + 5月の平均) を表示したいです。
使用しているフィールド：
以下のようにavg関数で平均を算出しています。
avg(
ifelse(
{分子} / ({分母1} / {分母2}) < 0,
0,
{分子} / ({分母1} / {分母2})
)
)
どうすれば、複数の月の平均値を合計した値を算出できますでしょうか？
ご質問頂きありがとうございます。
やりたいことを正確に把握したく、データサンプル、計算式、ビジュアルをご提示頂くことは可能でしょうか？
よろしくお願い致します。
具体的な情報は以下です。
（実際のデータから調整しておりますので数値は実際と異なります）
■使用データフィールド①：
avg( ifelse( {総コスト} / ({総生産量} / {目標生産量}) < 0, 0, {総コスト} / ({総生産量} / {目標生産量}) ) )
■データサンプル：
■データビジュアル：
以下のように①のデータフィールドを棒グラフに値として入れ表示しています。
今の状態ですと年月コントロールで２月分設定すると２月の合計値を平均された値が表示されます。
やりたいことは、店舗１の場合1.14+1.06=2.2となるように選択した月分の値をを加算して表示したいです。
あわせて棒グラフのビジュアル設定は以下です。
何卒宜しくお願い致します。
詳細ご説明頂きありがとうございます。
計算式を以下に修正すると、年月、店舗単位で平均値が算出されます。
avg(
ifelse(
総コスト / (総生産量 / 目標生産量) < 0, 0,
総コスト / (総生産量 / 目標生産量)
),
[年月,店舗]
)
これを使ってグラフを書くことで、年月ごとの店舗の平均値の合計をグラフ化できます。
以下、2ヶ月分データを作り、グラフ化してみました。
なお、元々の計算式ですが、総生産量が目標生産量未達の場合、０という条件でしょうか？計算したい条件は以下なのかな？と思い、こちらもご確認頂けると幸いです。
avg(
ifelse(
総生産量 / 目標生産量 < 0, 0,
総コスト / (総生産量 / 目標生産量)
),
[年月,店舗]
)
ありがとうございます。意図した動作が実現できました。
また、使用計算フィールドが以下の場合の式もご教示いただけるでしょうか。
そのままですとネストエラーになるためうまくいかず方法が知りたいです。
sum({総コスト}) / (coalesce({生産量スタッフ}, 0) + coalesce({生産量店長}, 0))
@chiemi.shimoda
ご連絡ありがとうございます。
計算式を以下に変更頂くとエラーになりません。
minは、maxでもavgでもOKです。
sum(総コスト) / min((coalesce(生産量スタッフ, 0) + coalesce(生産量店長, 0)))
Quick Sightは、集計関数と非集計関数を掛け合わせて計算フィールドを作成することができないため、min()を使って集計関数に変換することでエラーを回避できます。
ご返信いただきありがとうございます。
提示する情報が不足しており申し訳ないのですが、
この分母に使用しているフィールド
{生産量スタッフ}、{生産量店長}が既に分数式、sumで作成されています。
・{生産量スタッフ}＝SUM(ifelse({職位CD} = ‘02’,{総生産量｝, 0))
/
SUM(ifelse({職位CD} = ‘02’, {目標生産量}, 0))
・{生産量店長}＝SUM(ifelse({職位CD} = ‘01’,{総生産量｝, 0))
/
SUM(ifelse({職位CD} = ‘01’, {目標生産量}, 0))
——————————————————————————
そのため、先日ご教示いただいた以下avg関数を組み合わせる方法ですとネストエラーが発生しておりました。
今回ご教示いただいた下記フィールドでいくつか作成し試しましたが分母の影響かやはりネストエラーになってしまいます。
ーーーーーーーーーーーーーーーーーーーーーーーーーーー
・sum({総コスト}) / min((coalesce({生産量スタッフ}, 0) + coalesce({生産量店長}, 0)))
・sum({総コスト}) / min((coalesce({生産量スタッフ}, 0) + coalesce({生産量店長}, 0)), [年月, 店舗])
ーーーーーーーーーーーーーーーーーーーーーーーーーーー
年度フィルタで複数選択時に加算表示したいことから計算フィールド内で平均を算出したいです。
あわせて、棒グラフのX軸が半期単位で表示したいことから、x軸に年月フィールドを設定していなくとも動作するようにしたいです。
たびたびお手数をおかけしますが何卒宜しくお願い致します。
@chiemi.shimoda
ご連絡ありがとうございます。
coalesce()を使わなければ集計関数のみなのでエラーにはならないと思います。
coalesce()を使う理由は、職位CDがnull or 空白のレコードがあるというこでしょうか？
例えば、生産量を以下のような数式にすることで目的は達成できるでしょうか？
職位コード='01’が店長、'02’がスタッフの想定
{生産量} = ifelse(職位CD = '01' OR 職位CD = '02', 総生産量/目標生産量, 0)
上記で問題なければ、レコード単位で生産量が算出できるため、フィルターやピポットでサマリすることが問題なくできると思います。
各データ項目の名称と値のサンプルを詳しく教えて頂けると、もう少し事例ベースでお話しできると思います。よろしくお願い致します。
ご返信いただきありがとうございます。
検証したところ、やはり実装が難しそうなので恐縮ながらこちらの案は一旦保留となりました。
お忙しい中ご丁寧にご教示いただきありがとうございました。