はじめに

データ分析では年、半期、四半期、月などの単位で前期との比較や成長率の算出などが良く行われますが、ここではQlik Senseで用意されているAboveやBeforeなどの関数を利用してこれらの算出を行う手順をご紹介します。

準備

以下のリンクからサンプルデータをダウンロードし、ダウンロードしたZIPファイルを任意の場所に解凍してください。

>>サンプルデータ(店舗売上分析データ)のダウンロード

そして、新規アプリを作成してダウンロードしたZIPファイルに含まれるファイルを取り込んでください。もし、これらの手順が分からない場合には以下のエントリを参照してください。

>> 10分で試すQlik Senseでデータ分析

データを取り込むと以下の様なテーブルが作成されます。

image

基本的なAboveの利用

まず基本的なAboveの利用方法を見ていきたいと思います。Aboveの関数を利用すると、テーブル上で一つ上のレコードを取得することができます。

では、以下の形で一つ上のSum([売上])値を取得してみましょう。

Above(Sum([売上])) 

これにより、以下の形で一つ上の前月のレコードを取得できていることが分かるかと思います。(どの様な順でレコードが並んでいるか、ソート順の設定が重要となりますのでソートの設定にご注意下さい。

1

また、この数式を少し変更してみて以下の形とすると、前月からの成長率を計算することができます。

(Sum([売上])-Above(Sum([売上])))/Above(Sum([売上]))

image

同様な手順で軸に年、半期、四半期などを利用すれば、それぞれの単位で前期比を表示させることができます。

image

また、前月比ではなく前年同月比を計算したいケースもあると思いますが、その場合は「Above(Sum([売上]), 12)」といった形でAboveにもう一つの引数を数値で与えます。この「12」という数値は、一つ上ではなく「12個上」のレコードを取得する指定となり、例えば年月を軸として以下の数式を追加すると、12ヶ月前の前年同月比を算出することができます。

(Sum([売上])-Above(Sum([売上]),12))/Above(Sum([売上]),12)

image

ここまでの例ではテーブルを使ってご説明しましたが、棒グラフ、折れ線グラフ、コンボチャートなどにおいても同様の手法で前月比などを表示させることができます。

image

軸の絞り込みを行う際の注意点

一点注意が必要なのは、例えば以下の形で「2014-7」を選択から除外した場合、「2014-8」のAbove(Sum[売上]))の値は前月の「2014-7」ではなく、「2014-7」が除外された「2014-6」の値が表示されるということになります。

image

これを以下の形でSET分析を利用して「年月」の選択を無視する形にすると、「2014-7」が除外された場合でも無視され、「2014-8」の前月は「2014-7」として表示させることができます。(SET分析については「思い通りのチャート集計を実現–Qlik SenseのSET分析」のエントリをご参照ください。)

Above(Sum({$<年月>}[売上]))

image

これを以下の形でSET分析の{1}を利用して全ての月にゼロを追加し、全ての月を表示させる処理を加える方法もあります。ただし、この場合は7月の値が選択から除外されているため、「2014-8」の値は0となります。

Above(Sum([売上])) + Sum({1} 0)

image

軸が2軸以上ある場合

これまでは全て1軸でのみを扱ってきましたが、軸を2軸にした場合を見てみたいと思います。以下は、「年」と「年月」の2軸を追加したピボットテーブルを表示しています。

2

ここで注意が必要なのが、「年」でグループ化された中で「年月」の1つ上のレコードを取得するため、「2015-1」は年を跨って一つ上の「2014-12」を取得せずにヌル値の表示となってしまっています。

この様にヌル表示となっていて問題がない場合もありますが(例えば1軸目が「年」でなく「店舗」になっている場合等)、ヌル値の表示を避けるには以下の形で「Total」をAboveの中に追加します。そうすると年を跨って一つ上の「2014-12」値が取得されて「2015-1」に表示されます。

Above(Total Sum([売上]))

2

Aggr関数をAboveと組み合わせて同様のことを行うことも可能です。(Aggr関数は「Qlik SenseのAggrによる高度な集計処理」のエントリをご参照ください。)

Aggr(Above(Sum([売上])), 年月)

ただし、上記のケースではTotal、Aggrいずれを利用しても同様の結果が得られますが、Aggrを利用しなければいけないケースがあります。例えば以下の様なケースで、1軸目を「年月」、2軸目を「店舗」で設定すると、Totalを使った方法では思った通りに計算されません。

image

これは、例えば「2014-7」「店舗A」の一つ前は「2014-6」「店舗A」としたいのですが、テーブルの軸の並びが「年月」>「店舗」となってしまっているため、一つ前のデータが「2014-6」「店舗B」となってしまっていてそのデータを取得してしまっているからです。

この様な場合にはTotalではなく、以下の形でAggrを使った計算を行う必要があります。以下ではAggrによって「店舗」>「年月」の順で並んだ軸で内部的に作成された一時テーブルで計算がされるため、想定した値が出力されます。

Aggr(Above(Sum([売上])), 店舗,年月)

image

横方向での計算 – Before

これまでは1つ上のレコードを参照するAbove関数のみを利用してきましたが、以下の形で上下左右のレコードの位置を指定する関数がそれぞれ用意されています。

3

ピボットテーブルでは、これまで扱ってきた形で軸を行に配置するのではなく、列に配置することもできます。この場合に一つ前のレコードを取得するためには、上のレコードを参照するAbove関数ではなく、左側のレコードを参照するBefore関数を利用します。

Before(Sum([売上]))

image

そうすると、上記の形で左側の前月の値を取得することができます。(尚、Aggr関数を利用する場合には内部的な一時テーブルで集計がなされるため、この場合でもAbove関数を利用して集計を行うことができます。)

まとめ

ここでは、AboveやBeforeなどの関数を利用して、前期との比較や成長率の算出などを行う方法をご紹介しました。