集計関数のグラフ化について

Ogino · October 25, 2023, 4:39am

QuickSightでABC分析をしたいと考えております。
例えば販売データから来店頻度について調査し、ABCのランク分けを行い、それぞれの人数を棒グラフにしたいとします。

複数商品を購入する場合もあるため、販売日時を重複なしでまとめる「distinct_count」を用いて来店回数を計算
集計関数である「percentile」を用いて閾値を導く。ただし、集計した数値とそれぞれの来店回数を比較できないため、「percentileOver」を利用して人単位で閾値を比較出来るように修正
「ifelse」を用いて比較を行いABCのランク分けを実施

最終的な計算式は以下のようになりました。

ifelse(
percentileOver(percentile(distinct_count(日時, [{来店者の識別番号}]), ${閾値パラメータ}), ${閾値パラメータ}) <= distinct_count(日時),
'A',
ifelse(
percentileOver(percentile(distinct_count(日時, [{来店者の識別番号}]), ${閾値パラメータ}), ${閾値パラメータ}) <= distinct_count(日時),
'B',
'C'
)
)

この方法でABC分けの表は完成しました。しかし下記の問題が発生しています。

・集計関数を使用しているため、ABCそれぞれ何人いるのかを棒グラフにしようとするとエラーとなる
・集計関数を使用した計算フィールドはそもそもディメンションに出来ない
・distinctCountOverやpercentileOverなどを使用して集計関数を使用せずに進めようと試みたが、percentileを使用する以上必ず集計をしなければいけない認識でうまくいかない

この問題を解決するための方法はございますでしょうか？
よろしくお願いいたします。

Wakana · October 25, 2023, 1:29pm

@Ogino 様

QuickSight Communityにご質問いただき、ありがとうございます！

ご認識の通り、集計した数値と未集計の数値を比較することはできません。ただ、LAC（レベルアウェア計算）関数を使用すると、ビジュアル集計を実施する前に集計計算を実施することでき、その比較をすることができるようになります。どのようなビジュアル設定でご利用いただくのかわからないので、いただいた情報からのみの推測にはなりますが、以下のような計算式で対応できないでしょうか？

ifelse(percentileOver(distinctCountOver(日時, [{来店者の識別番号}],PRE_AGG), ${閾値パラメータ}, [], PRE_AGG) <= distinctCountOver(日時, [], PRE_AGG), 'A', 'C')

また、LAC関数については、以下にブログがあります。

以下のワークショップでも、LACについては体験いただけるようになっております。

Workshops -- Amazon QuickSight のダッシュボード作成を体験しましょう | Japanese QuickSight: Getting Started

日本語でQuickSightのダッシュボード作成の体験ができます。 Amazon QuickSight – Visualization Basics (Japanese) - 初めてダッシュボードを作成する方にも作成いただけるようステップ・バイ・ステップで手順が記載されています。また、少し高度な機能を利用したダッシュボードやレポート作成など、QuickSightの機能を網羅的に体験いただけるように構成されています。 Amazon QuickSight – 埋め込み (Japanese) - 作成したダッシュボードをWebサイトに埋め込みたい場合は、このワークショップにて体験いただけます。サンプルアプリケーションが提供されており、ステップ・バイ・ステップの手順で、匿名認証やユーザ認証をして埋め込むことができます。

Ogino · October 26, 2023, 4:31am

@Wakana 様

計算式のご提案ありがとうございます。少し改良しまして、

ifelse(percentileOver(distinctCountOver(日時, [{来店者の識別番号}], PRE_AGG), ${閾値パラメータ}, [], PRE_AGG) <= distinctCountOver(日時, [{来店者の識別番号}], PRE_AGG), 'A', 'C')

としたところ期待していた棒グラフが描画できました。大変感謝いたします。

ただ、結果が期待していたものと異なっておりました。
原因としてはpercentileOverの結果が明らかに大きく、Aランクに振り分けられた数がとても少なくなってしまいました。

他BIツール等で確認したところ

percentile(distinct_count(日時, [{来店者の識別番号}]), ${閾値パラメータ})
(閾値パラメータは現在70指定)

で求められる値が正解のようです。もちろんこちらはLAC関数ではないので、グラフの描画には使用できませんが、現在の

percentileOver(distinctCountOver(日時, [{来店者の識別番号}], PRE_AGG), ${閾値パラメータ}, [], PRE_AGG)

と求めようとしている値は同じはずだと考えております。
どこか私の認識違いがあればご教示いただけますと幸いです。

よろしくお願いいたします。

Wakana · October 26, 2023, 5:52am

@Ogino 様

ご連絡ありがとうございます。

LAC計算は、ビジュアルで設定した集計より先に実施してしまうので、ビジュアル設定の内容を考慮しないと、最終的な計算結果に差異がでてきてしまいます。

今回ご利用になっている棒グラフの設定（フィールドウェルの設定）についてご教示いただけないでしょうか？スクリーンショットでも問題ありません。

Ogino · October 26, 2023, 8:36am

@Wakana 様

ご連絡ありがとうございます。

フィールドウェルと棒グラフになります。

フィールドウェルの「ABC_Score」は前の投稿にある計算式。「no」は識別番号となります。
コントロールのA_rankが閾値パラメータです。
設定値が70であれば、A:Cが3割:7割になるように表示することが私の実現したいグラフになります。

よろしくお願いいたします。

Wakana · October 26, 2023, 2:22pm

@Ogino 様

ご連絡いただき、ありがとうございます。
「no」はレコード識別番号と理解し、同じようなシナリオで私の環境でも確認してみましたが、Aの値が３割程度の表示になります。

念のために、以下の計算式で再度試していただいてもいいでしょうか？

ifelse(percentileDiscOver(distinctCountOver(日時, [{来店者の識別番号}], PRE_FILTER), ${閾値パラメータ}, [], PRE_FILTER) <= distinctCountOver(日時, [{来店者の識別番号}], PRE_FILTER), 'A', 'C')

Ogino · October 30, 2023, 6:36am

@Wakana 様

ご連絡ありがとうございます。3対7になっているんですね。
ご提案頂いた計算式でも同じような結果となりました。
「no」は識別番号にはなりますが、販売データの形としては

no	日時	数量	値段
001	20231030 10:00:00	1	1000
001	20231030 10:00:00	3	600
003	20231030 10:05:00	1	300

(イメージ)

のような形になっております。
識別番号ごとに集計されたデータではなく、販売時のデータです。
同じ認識でしたでしょうか？
同じ認識であった場合は、データや他計算式の見直しをする必要があるという理解でおります。

よろしくお願いいたします。

Wakana · October 30, 2023, 12:57pm

@Ogino 様
ありがとうございます。

「no」は重複しているようですね。それであれば、以下の計算式ではいかがでしょうか？

ifelse(percentileDiscOver(distinctCountOver(日時, [{来店者の識別番号}], PRE_AGG), ${閾値パラメータ}, [{no}], PRE_AGG) <= distinctCountOver(日時, [{来店者の識別番号}], PRE_AGG), 'A', 'C')

Ogino · October 31, 2023, 10:18am

@Wakana 様

ありがとうございます。
早速試してみましたが、全てAに振り分けられてしまいました。

percentileDiscOver(distinctCountOver(日時, [{来店者の識別番号}], PRE_AGG), ${閾値パラメータ}, [{no}], PRE_AGG)

この値のみ抜き出して結果を表示すると、想定している値になっていないようです。
私のLAC関数の理解不足の可能性が高いですが…

集計してしまうためグラフ化できませんが、下記の計算式で想定通りの値となります。

percentileContOver(avg(distinctCountOver(日時, [no], PRE_AGG)), 70)

このあたりの理解が進めば実現できると思うのですが、なにか認識違いがあればご指摘いただければ幸いです。
よろしくお願いいたします。

Wakana · November 1, 2023, 3:08am

@Ogino 様

ご連絡ありがとうございます。

percentileContを使用すると、データセットのデータ以外のLinearデータを考慮し正確なPercentileを返すことができます。それに対し、percentileDiscだと、データセットのデータに基づいたPerentileを返します。例えば、以下のようなデータセットだと、それぞれ異なる値を返していることがわかります。

今回は、Percentileの値をifelse()にて判定を実施しないといけなく、集計した計算フィールドをディメンション指定できないという問題に対応するため、LAC関数であるPercentileDiscOver、PercentileContOverという関数を使用し、事前にPercentile計算を実施する必要があります。この関数はWindow関数であるため、上記ご指摘あったように集計されてしまうため、その値を取得するにはMin（最小値）集計する必要があります。
以下の例は、このLACのWindow関数を使用して、ディメンションを指定せずにPercentileを算出した例です。
ディメンションを指定せずにPercentileを算出した例です。

同じLACのWindow関数を使用して、Group別にPercentileを計算すると以下のようになります。

これらの情報が少しでもご理解に役立てると幸いです。

Wakana · November 6, 2023, 1:58am

@Ogino 様

その後いかがでしょうか？もし正常にフラグが生成できておられないようでしたら、ぜひお知らせください。よろしくお願いします。

Ogino · November 7, 2023, 6:52am

@Wakana 様

ご連絡ありがとうございます。試しに様々なデータを作成しながら動作の確認を行っておりました。
認識の確認をさせていただきたいです。

私が今回のデータソースとしていたものは来店者の識別番号で集計したものではなく、何時に誰が何を買ったかのデータでした。
(識別番号よりも会員番号と言った方が分かりやすいので変更します。)

会員番号	時間	個数	金額
1	2023-11-01 10:00:00	1	300
2	2023-11-01 14:30:00	2	500
2	2023-11-01 14:30:00	1	500
2	2023-11-01 14:30:00	3	300
1	2023-11-02 10:30:00	1	600
1	2023-11-02 10:30:00	1	100
3	2023-11-03 12:00:00	1	1000

この場合は、

会員番号1番が11/1の10時に300円のものを1つ購入
会員番号2番が11/1の14時半に250円のものを2つ、500円を1つ、100円を3つ購入
会員番号1番が11/2の10時半に600円のものを1つ、100円を1つ購入
会員番号3番が11/3の12時に1000円のものを1つ購入

という形です。これを➀とします。

会員番号で集計すると

会員番号	来店回数	総購入品数	総購入金額
1	2	3	1000
2	1	6	1300
3	1	1	1000

となり、これを➁とします。

➁の状態がデータソースとなっているものを試したところ、ABCのランク付けは正常に出来ました。
(データは作成したもので実際のデータではありません)

➀がデータソースとなっている場合にグラフや図を表示をしたいと考えておりましたが、
➀→➁の集計を(distinctCountOverやsumOver等で)QuickSight上にて行った後に、グラフ化することは現状難しいのでしょうか？

よろしくお願いいたします。

Wakana · November 7, 2023, 7:59am

@Ogino 様

来店者の識別番号とNo(識別番号）の整理をいただき、ありがとうございます。

①から②をLACで実施して、ifelse判定でグラフ化できる認識です。上記いただいた情報から、Percentile計算を来店回数で実施していると推測で、以下ではいかがでしょうか？

来店回数(LAC) : distinctCountOver(番号時間,[会員],PRE_AGG)

AB基準値_70(LAC) : percentileDiscOver({来店回数(LAC)},70,[],PRE_AGG)

BC基準値_30(LAC) : percentileDiscOver({来店回数(LAC)},30,[],PRE_AGG)

ABC Rank: ifelse({来店回数(LAC)} >= {AB基準値_70(LAC)},'A',{来店回数(LAC)} >= {BC基準値_30（LAC)},'B','C')

Ogino · November 8, 2023, 4:52am

@Wakana

ありがとうございます。
ご提案いただいた計算式を試してみたのですが、やはりAの人数がとても少なくなってしまいます。
percentileの値がやけに大きいのでそのあたりの勉強不足かもしれません。

例えば下記のような来店回数を70%でpercentileしたときは7か8くらいになると考えており、大きい値が一つあったとしてもpercentileの計算では特に問題ではないという考えですが、その認識であっていますでしょうか？

会員番号	来店回数
NULL	100000
2	10
3	8
4	7
5	5
6	5
7	2
8	1

上記の画像が実際の画面なのですが、84ページ×500で約40000件のデータがあります。ここでpercentileの70%とすると、40000*0.7で上位から約12000件目あたりの来店回数がABの基準となって欲しいです。
ただ実際のAB基準値は532となっており、532は1ページ目にある会員番号の来店回数となっております。ここでAの人数が極端に少なくなっていると思われます。

ここが解決できれば想定どおりのグラフが描けると思います。
度々すみませんが、ご確認よろしくお願いいたします。

Wakana · November 9, 2023, 10:12pm

＠Ogino 様

詳細を調査いただきありがとうございます。

今内部でpercentile関数の仕様を確認させていただいています。確認でき次第、報告させていただきます。よろしくお願いします。

Ogino · November 10, 2023, 12:14am

@Wakana 様

ご連絡ありがとうございます。
仕様を確認していただけるとのこと、ありがとうございます。かしこまりました。
引き続き私の方でもデータの不備等がないか、試行錯誤してみます。

よろしくお願いいたします。

Wakana · November 16, 2023, 5:20am

@Ogino様

長くお待たせして申し訳ありません。
内部の使用が確認ができたので、以下のように、計算フィールドを変更してPercentile70での計算ができているかご確認いただけないでしょうか？

来店回数(LAC) : distinctCountOver(番号時間,[会員],PRE_AGG)

AB基準値_70(LAC) : percentileDiscOver(min({来店回数(LAC)}),70,[],POST_AGG_FILTER)

よろしくお願いします。

Ogino · November 16, 2023, 6:55am

@Wakana 様

ご確認いただき誠にありがとうございます。

AB基準値_70(LAC) : percentileDiscOver(min({来店回数(LAC)}),70,,POST_AGG_FILTER)

こちらであれば、Percentile70の値は想定通りとなりました。

Wakana · November 16, 2023, 7:19am

＠Ogino様

ご連絡ありがとうございます！

であれば、ランクづけも以下ができるように思います。

BC基準値_30(LAC) : percentileDiscOver(min{来店回数(LAC)},30,[],POST_AGG_FILTER)

ABC Rank: ifelse(min{来店回数(LAC)} >= {AB基準値_70(LAC)}, 'A' ,min({来店回数(LAC)}) >= {BC基準値_30（LAC)}, 'B', 'C')

Ogino · November 16, 2023, 10:02am

@Wakana 様

ありがとうございます。
ただしこの場合は集計してしまっているので、グラフ化は出来ない認識なのですがお間違いないでしょうか？
8番目のやり取り(10/30)の状態と同じ理由かと思われます。

よろしくお願いいたします。