QlikViewでP関数を使ってベン図(Google Charts)を作成する

今回のエッセンスは２つある。
①QlikViewにないベン図をGoogle Chartsで実現する
②「A ∩ B」のような積集合をP関数を使用して実現する
ちょっと応用編ではあるけど、これできたらちょっと素敵じゃない？

#今日のお題
今回のお題はこんなありがちなやつ。
・10人の顧客がいる
・商品のカテゴリが5種類ある
・顧客によってどのカテゴリを購入しているパターンが多いかを見たい
・その時、複数のカテゴリにまたがって購入しているパターンがどれくらいかも把握したい

QlikViewの標準チャートでは難しいよね。
なので今回はベン図を使っちゃう。

#まずはデータ作成

まずはデータの作成。
今回は顧客が10人、商品カテゴリは5種類、データは50件、2020/1/1から1年間のデータを自動生成してみる。

データ作成

LET vCustomerCnt =10;
LET vCategoryCnt = 5;
LET vTranCnt = 50;
LET vDateStart = 43831;
LET vDuration = 365;

Customer:
LOAD RowNo()            as customer_id,
     Hash128(RowNo())   as customer_name
AutoGenerate $(vCustomerCnt);

Category:
LOAD RowNo()                as category_id,
     'Category_' & RowNo()  as category_name
AutoGenerate $(vCategoryCnt);

Transaction:
LOAD RowNo()                                        as tran_id,
     Date($(vDateStart) + Floor((Rand() * $(vDuration))),'YYYY/MM/DD') as date, 
     Ceil(Rand()*$(vCustomerCnt))                   as customer_id,
     Ceil(Rand()*$(vCategoryCnt))                   as category_id,
     Mod(Floor(pow(Rand(),2)*10),3)+1               as unit,
     Ceil(Sqrt(Rand()*10000000))                    as amount
AutoGenerate $(vTranCnt);

MasterCalendar:
LOAD tmp_date                                       as date,
     Year(tmp_date)                                 as year,
     Month(tmp_date)                                as month,
     Date(MonthStart(tmp_date),'YYYY/MM')           as yearmonth;
LOAD
    Date(mindate + IterNo())                        as tmp_date,
    maxdate
WHILE mindate + IterNo() <= maxdate;
LOAD min(FieldValue('date',RecNo()))-1              as mindate,
     max(FieldValue('date',RecNo()))                as maxdate
AutoGenerate FieldValueCount('date');

#P関数とは
僕はSET分析を教える時、「これはロジックではなくて、選択状態を式にしたもの」と伝えるようにしている。
そこで、SET分析でP関数を使うメリットは何かというと、暗黙の選択を指定できるという点だ。
暗黙の選択？
言い換えると「間接的な選択」

例えば、
count(distinct customer_id)
という標準式があるとすると

数式

count(distinct{$<categoty_name={'Category_1'}>} customer_id)

とすれば
Category_1を購入した顧客数が算出できる。

では「Category_1」を購入した顧客は（Category_1以外の購入回数も含めて）全部で何回購入してるんだっけ？
という場合はどうなる？

数式

count(distinct {$<categoty_name={'Category_1'}>} tran_id)

これだと、categoty_nameが'Category_1'で絞り込まれているから、'Category_1'の購入回数しか計算できないよね。

そんな時に計算を可能にするのがP関数だ。
正解は

数式

count(distinct　{$<customer_name = P({$<categoty_name={'Category_1'}>})>} tran_id)

である。
解説すると、顧客の条件は「P()で絞り込まれた顧客」を意味し、categoty_name={'Category_1'}そのものは、tran_idの計算には使われない。P()の中にSET文はcategoty_nameを絞り込むのに使用されている。

#P関数の積集合
P関数では積集合が使える（もちろん和集合やその他も）。
Category_1とCategory_2の両方を購入した顧客の購入回数の合計ということなら、

数式

count(distinct　{$<customer_name = P({$<categoty_name={'Category_1'}>})*P({$<categoty_name={'Category_2'}>})>} tran_id)

と書けばいい。
つまり、P()*P()だ。
P()の逆はE()で、

数式

count(distinct　{$<customer_name = P({$<categoty_name={'Category_1'}>})*E({$<categoty_name={'Category_2'}>})>} tran_id)

とすると
Category_1を購入しているがCategory_2を購入していない顧客の購入回数の合計ということになる。

どう、わかった？

#チャートの作成
ますはカテゴリは全部で5個あるから、そこから3つのカテゴリを選択するためのドロップダウンリトを作成する。ドロップダウンリストの作成方法は
QlikViewで２つの店舗をドロップダウンリストで選択して比較してみる
を参考にしてほしい。

因みにリスト値の数式はこう。
Concat({1}DISTINCT category_name,',')

これで、3つのカテゴリを選択できるようになる。
ここで、vSelect1とvSelect2のカテゴリ両方のカテゴリの商品を購入した顧客数はこうなるよね。

数式

Count(DISTINCT {$<customer_id=P({$<category_name={$(vSelect1)}>})*P({$<category_name={$(vSelect2)}>})>} customer_id)

このやり方で
A ∩ B
A ∩ C
B ∩ C
A ∩ B ∩ C
も同様に数式が書ける。

#Google ChartsのVenn Diagram

Google Charts APIを使うと、QlikViewのアプリからGoogle Chartsの様々なチャートを表示できるようになる（Google ChartsはWeb接続が可能な環境でした表示できないから注意）。
今回はベン図(Venn Diagram)なので以下のページに仕様が公開されている。

Venn Charts | Image Charts | Google Developers

ではオブジェクトを作成していこう。

#QlikViewのアプリ内に実装

QlikViewのアプリにGoogle Chartsを表示するためにはテキストオブジェクトを使用する。シート上にテキストオブジェクトを配置して下記の設定を行う。

基本設定タブで「テキスト」に下記の数式を入力する。
（プレゼンテーションはデフォルトが「テキスト」だが「画像」ｂに変更しておく）

数式

='http://chart.apis.google.com/chart?cht=v&chs=400x200&'&
'chdl=$(vSelect1)|$(vSelect2)|$(vSelect3)&'&
'chco=FF6342,FFD700,63C6DE&'&
'chd=t:'&
Count(DISTINCT {$<customer_id=P({$<category_name={$(vSelect1)}>})>} customer_id)
&','&
Count(DISTINCT {$<customer_id=P({$<category_name={$(vSelect2)}>})>} customer_id)
&','&
Count(DISTINCT {$<customer_id=P({$<category_name={$(vSelect3)}>})>} customer_id)
&','&
Count(DISTINCT {$<customer_id=P({$<category_name={$(vSelect1)}>})*P({$<category_name={$(vSelect2)}>})>} customer_id)
&','&
Count(DISTINCT {$<customer_id=P({$<category_name={$(vSelect1)}>})*P({$<category_name={$(vSelect3)}>})>} customer_id)
&','&
Count(DISTINCT {$<customer_id=P({$<category_name={$(vSelect2)}>})*P({$<category_name={$(vSelect3)}>})>} customer_id)
&','&
Count(DISTINCT {$<customer_id=P({$<category_name={$(vSelect1)}>})*P({$<category_name={$(vSelect2)}>})*P({$<category_name={$(vSelect2)}>})>} customer_id)

【数式の解説】
・１行目の'http://chart.apis.google.com/chart?cht=v&chs=400x200&'
ここではGoogle Charts APIを指定し、cht=vでチャートタイプ＝ベン図、chs=400x200でチャートのサイズを設定している。
・2行目の'chdl=$(vSelect1)|$(vSelect2)|$(vSelect3)&'
ここでは凡例の指定を行っている。
・3行目の'chco=FF6342,FFD700,63C6DE&'
ここでが3の円の色を指定している。それぞれvSelect1,vSelect2,vSelect3の色が設定されている。
・4行目移行の'chd=t:'
ここでは数式を指定する。数式の指定は
chd=t:A,B,C,A∩B,A∩C,B∩C,A∩B∩C
の順番と決まっており、それぞれに計算式をセットする。
Google Chartsのベン図は3つの要素と決まっており、変更することはできない（つまり4つ以上の要素のベン図は表現できない）。2つの要素でベン図を書きたいときは
chd=t:A,B,,A∩B
と指定すればよい。

その他、数式については上のP関数の例でおわかりいただけたと思うので、ここでは説明を省略。

#結果
上記のように設定が完了すると以下のようなベン図ができあがる。

ドロップダウンでカテゴリを選択し直すとベン図が即座に変わるはず。
因みにGoogle ChartsはQlikViewの標準オブジェクトではないから、クリックしても何も起こらない（単なる画像）。

#まとめ
Google Chartsのベン図を使用してみた。Google ChartsにはMap Chartなど、QlikViewで標準サポートされていない様々なチャートがある。
機会があればMap Chartも紹介していきたいと思う。
また、P関数はわかっていただけたかな？
これを覚えたら最強だよ。
みなさんも今作っているアプリで取り入れてみてね。

ではまた！