Qlik Senseで思い通りのチャート集計を実現–SET分析

[Masaki_Hamano]

 

■ SET分析とは？

Qlik Senseでは、例えば「年」のフィルタで「2021年」を選択すると全てのチャートに対して絞り込みが反映され、集計範囲を絞って分析を行うことができます。
その中で今回ご紹介する「SET分析」は例外で、どのような年の選択絞り込みを行っても特定のチャートでは常に最新年を表示したり、細かい条件を指定してその条件に合致する範囲でのみ集計対象とするなど、多岐に渡った集計の手法を活用することができます。ここではこのSET分析についてご説明します。

■ 事前の準備

以下のリンクからサンプルデータをダウンロードし、ダウンロードしたZIPファイルを任意の場所に解凍してください。このデータを使って、このエントリでご説明する式などを試してみることができます。

サンプルデータ(SET分析用データ)のダウンロード

まず、Qlik Senseを開いて名前の新規アプリを作成し、ダウンロードしたZIPファイルに含まれるファイルを取り込んでください。

■ 基本的な構文

では、まずSET分析の基本的な構文についてご説明します。

SET分析は集計関数の内部に記述します。以下の式を例にとってSET分析を記述する流れを3つのステップでご説明したいと思います。

Sum([販売価格]) 

ステップ1：波括弧の追加

SET分析を記述する場所のマーカーとして波括弧を追加します。

Sum({} [販売価格]) 

ステップ2 : SET識別子の追加

波括弧の中にSET識別子($, 1, 1-$)を追加します。以下の例では「現在の選択範囲」を表す「$」を追加しています。

Sum({$}[販売価格])

ここでは「どの範囲のレコードセットを対象とするか？」を指定し、主には以下の「$」, 「1」, 「1-$」の3つになります。

例えば「都道府県」で「東京」を選択している場合、「Sum({$} [販売価格])」では「東京」のみを範囲の対象とします。「Sum({1} [販売価格])」ではその選択条件を無視して全都道府県を集計の対象とし、「Sum({1-$} [販売価格])」では「東京」以外を対象とします。

ステップ3 : SET修飾子の追加

ステップ2でレコードセットの大枠の範囲を定義し、このステップ3でさらに細かい条件を指定してその範囲を調整します。まず、「<>」を「$」の後に追加し、その中に条件を記述します。

Sum({$<年 = {2021}>} [販売価格])

上記の例では現在の選択範囲から「年」が「2021年」のみを対象として集計を行います。このSET修飾子は<軸名 = {メンバー一覧}, 軸名 = {メンバー一覧}・・・>という形式で記述ができ、以下の様な形で複数の軸を条件に含まることも可能です。

Sum({$<年 = {2021}, 月 = {1}>} [販売価格])

また、以下のSET演算子を利用することも出来ます。

+ : 和集合(Union)
– : 排除(Exclusion)
* : 共通集合(Intersection)
/ : 排他的論理和

例えば、以下の場合には現在の選択範囲に「2021年」が含まれない場合にも「2021年」が追加されて集計が行われます。

Sum({$<年 += {2021}>} [販売価格])

また、逆に以下の場合には「2021年」を除外して集計が行われます。

Sum({$<年 -= {2021}>} [販売価格])

このSET修飾子には多様な条件を設定することができます。次の章で幾つかのパターンをご紹介したいと思います。

■SET修飾子のパターン

メンバーの指定

基本的な構文の例でご説明した通り、SET識別子に<軸名 = {メンバー一覧}>という形式で指定を行え、以下の形でメンバーを{}内にカンマ区切りで複数指定することもできます。

Sum({$<年 = {2020, 2021}>} [販売価格])

メンバーの文字列に「-」やスペースなどが含まれる場合には以下の形でクオートで囲む必要があります。

Sum({$<商品名 = {'8畳用エアコン H22SX','12畳用エアコン F21SX'}>} [販売価格])

文字列検索

以下を使った文字列検索を行って対象を特定することが出来ます。

* : 任意の文字列(ワイルドカード)
? : 1文字

以下の例では商品名に「エアコン」の文字列を含む商品を対象とします。

Sum({$<商品名 = {"*エアコン*"}>} [販売価格])

特定軸の選択条件無視

<軸名 = >の形式でメンバーを空白で指定すると、その軸での選択を無視して全てのレコードを集計の対象とすることが出来ます。(<軸名 = {*}>という形式での記述も可能です)

Sum({$<年 =, 商品名 =>} [販売価格])

ただし、以下の形で「年月」という他の列項目で年の範囲が絞り込まれた場合、その選択は反映されますので、この点留意が必要です。

数値の範囲指定

以下の様な形で数値の範囲を指定することが可能です。以下の例では3月以降のみを集計の対象とします。

Sum({$<月={">2"}>} [販売価格])

また、以下の形で上限・下限を指定することも可能です。

Sum({$<月={">=4 <=6"}>} [販売価格])

変数の利用

SET識別子で変数を利用することも可能です。以下では「vProducts」という変数を作成し、定義に「"8畳用エアコン H22SX","12畳用エアコン F21SX"」という値が設定されています。

この時、以下の形でこのvProductsの変数を利用することが出来ます。

Sum({$<商品名 = {$(vProducts)}>} [販売価格])

同様に、数値の範囲指定などでもvStartMonthとvEndMonthの変数を作成して、以下の形で変数を利用することも可能です。

Sum({$<月={">=$(vStartMonth) <=$(vEndMonth)"}>} [販売価格])

関数の利用

関数は<軸 = {“$(=関数(引数))”}>という形式で利用することが可能です。以下の例ではMax関数を使い、現在の選択範囲の中での最新年のみを集計の対象とします。

Sum({$<年 = {"$(=Max(年))"}>} [販売価格])

また、このSET識別子の中の関数にSET修飾子を付加することも可能で、以下の場合には現在の選択を無視して全体から最新年を返します。

Sum({$<年 = {"$(=Max({1} 年))"}>} [販売価格])

以下の様な形で-1を追加すると最新年-1を対象とすることが出来ますので、上記の数式と組み合わせて前年比などの計算を行うことができます。

Sum({$<年 = {"$(=Max(年)-1)"}>} [販売価格])

メンバーを返す関数の利用

SET識別子で関数を利用し、そこで指定した条件に合致するメンバーのみを集計の対象とすることが出来ます。以下の例では、「販売価格の合計が10,000,000円を超える商品」を集計の対象としています。

Sum({$<商品名={"=Sum([販売価格])>10000000"}>} [販売価格])

また、内部の関数にもSET分析を適用して「2020年に500万円の売上を超えた商品」を集計の対象とするようなこともできます。

Sum({$<商品名={"=Sum({1<年 = {2020}>} [販売価格])>5000000"}>} [販売価格])

P()とE()の利用

P()とE()はQlik Senseの連想技術を活用した集計範囲の絞り込みを行うことができます。例えば、「デスクトップパソコンが販売されている伝票のみに絞り込んで売上の集計を行いたい」といったケースを考えます。この様な場合、以下のイメージの様に、マニュアル作業で「商品名」から「デスクトップパソコン」を選択すると(緑色でハイライト)、この商品と紐付く伝票番号は白色で表示されてそれ以外は灰色で表示されますので、この白色で表示された伝票番号を選択することで絞り込みは行うことが出来ます。

この様な作業に相当することを行えるのがP()とE()になります。P()は上記の白色の項目に対応する「可能な値(Possible)」を返すために利用でき、例えば以下によって「商品名」が「デスクトップパソコン」を選択した時に「可能な値」となる「伝票番号」のみを絞り込むことが出来ます。

Sum({$<伝票番号 = P({1<商品名={'デスクトップパソコン'}>})>} [販売価格])

また逆にE()は上記の灰色の項目に対応する「除外値(Excluded)」を返し、以下の例では上記の灰色の伝票番号を集計の対象とします。

Sum({$<伝票番号 = E({1<商品名={'デスクトップパソコン'}>})>} [販売価格])

2つのフィールドの比較

<軸 = {“=(真偽の判別)”}>という形式で、2つのフィールドを比較して合致するもののみを集計の対象とすることが出来ます。以下の例では、販売日付と出荷日付が同じ伝票のみを集計の対象としています。

=Sum({$<伝票番号 = {"=(出荷日付 = 販売日付)"}>} [販売価格])

また、以下の様な形で「販売から7日以内に出荷された伝票」のみに絞り込むことも出来ます。

Sum({$<伝票番号 = {"=(出荷日付 - 販売日付 < 7)"}>} [販売価格])

■ まとめ

以上、SET分析構文の基本と、パターンに分けたSET分析の利用方法をご説明しました。ここでご説明した通り、SET分析を利用することで、思い通りのチャート集計を実現するために多岐に渡った手法を用いることができます。

SET分析データ.zip