Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
はじめに
データ分析では年、半期、四半期、月などの単位で前期との比較や成長率の算出などが良く行われますが、ここではQlik Senseで用意されているAboveやBeforeなどの関数を利用してこれらの算出を行う手順をご紹介します。
準備
サンプルデータを利用して、ご説明する操作を実際に試して頂くことができます。以下のリンクからサンプルデータをダウンロードし、ダウンロードしたZIPファイルを任意の場所に解凍してください。
そして、新規アプリを作成してダウンロードしたZIPファイルに含まれるファイルを取り込んでください。
基本的なAboveの利用
まず基本的なAboveの利用方法を見ていきたいと思います。Aboveの関数を利用すると、テーブル上で一つ上のレコードを取得することができます。
では、以下の形で一つ上のSum([売上])値を取得してみましょう。
Above(Sum([売上]))
これにより、以下の形で一つ上の前月のレコードを取得できていることが分かるかと思います。（どの様な順でレコードが並んでいるか、ソート順の設定が重要となりますのでソートの設定にご注意下さい。）
また、この数式を少し変更してみて以下の形とすると、前月からの成長率を計算することができます。
(Sum([売上])-Above(Sum([売上])))/Above(Sum([売上]))
同様な手順で軸に年、半期、四半期などを利用すれば、それぞれの単位で前期比を表示させることができます。
また、前月比ではなく前年同月比を計算したいケースもあると思いますが、その場合は「Above(Sum([売上]), 12)」といった形でAboveにもう一つの引数を数値で与えます。この「12」という数値は、一つ上ではなく「12個上」のレコードを取得する指定となり、例えば年月を軸として以下の数式を追加すると、12ヶ月前の前年同月比を算出することができます。
(Sum([売上])-Above(Sum([売上]),12))/Above(Sum([売上]),12)
ここまでの例ではテーブルを使ってご説明しましたが、棒グラフ、折れ線グラフ、コンボチャートなどにおいても同様の手法で前月比などを表示させることができます。
軸の絞り込みを行う際の注意点
一点注意が必要なのは、例えば以下の形で「2014-7」を選択から除外した場合、「2014-8」のAbove(Sum[売上]))の値は前月の「2014-7」ではなく、「2014-7」が除外された「2014-6」の値が表示されるということになります。
これを以下の形でSET分析を利用して「年月」の選択を無視する形にすると、「2014-7」が除外された場合でも無視され、「2014-8」の前月は「2014-7」として表示させることができます。(SET分析については「思い通りのチャート集計を実現–Qlik SenseのSET分析」のエントリをご参照ください。)
Above(Sum({$<年月>}[売上]))
これを以下の形でSET分析の{1}を利用して全ての月にゼロを追加し、全ての月を表示させる処理を加える方法もあります。ただし、この場合は7月の値が選択から除外されているため、「2014-8」の値は0となります。
Above(Sum([売上])) + Sum({1} 0)
軸が2軸以上ある場合
これまでは全て1軸でのみを扱ってきましたが、軸を2軸にした場合を見てみたいと思います。以下は、「年」と「年月」の2軸を追加したピボットテーブルを表示しています。
ここで注意が必要なのが、「年」でグループ化された中で「年月」の1つ上のレコードを取得するため、「2015-1」は年を跨って一つ上の「2014-12」を取得せずにヌル値の表示となってしまっています。
この様にヌル表示となっていて問題がない場合もありますが(例えば1軸目が「年」でなく「店舗」になっている場合等)、ヌル値の表示を避けるには以下の形で「Total」をAboveの中に追加します。そうすると年を跨って一つ上の「2014-12」値が取得されて「2015-1」に表示されます。
Above(Total Sum([売上]))
Aggr関数をAboveと組み合わせて同様のことを行うことも可能です。
Aggr(Above(Sum([売上])), 年月)
ただし、上記のケースではTotal、Aggrいずれを利用しても同様の結果が得られますが、Aggrを利用しなければいけないケースがあります。例えば以下の様なケースで、1軸目を「年月」、2軸目を「店舗」で設定すると、Totalを使った方法では思った通りに計算されません。
これは、例えば「2014-7」「店舗A」の一つ前は「2014-6」「店舗A」としたいのですが、テーブルの軸の並びが「年月」＞「店舗」となってしまっているため、一つ前のデータが「2014-6」「店舗B」となってしまっていてそのデータを取得してしまっているからです。
この様な場合にはTotalではなく、以下の形でAggrを使った計算を行う必要があります。以下ではAggrによって「店舗」＞「年月」の順で並んだ軸で内部的に作成された一時テーブルで計算がされるため、想定した値が出力されます。
Aggr(Above(Sum([売上])), 店舗,年月)
横方向での計算 – Before
これまでは1つ上のレコードを参照するAbove関数のみを利用してきましたが、以下の形で上下左右のレコードの位置を指定する関数がそれぞれ用意されています。
ピボットテーブルでは、これまで扱ってきた形で軸を行に配置するのではなく、列に配置することもできます。この場合に一つ前のレコードを取得するためには、上のレコードを参照するAbove関数ではなく、左側のレコードを参照するBefore関数を利用します。
Before(Sum([売上]))
そうすると、上記の形で左側の前月の値を取得することができます。(尚、Aggr関数を利用する場合には内部的な一時テーブルで集計がなされるため、この場合でもAbove関数を利用して集計を行うことができます。)
まとめ
ここでは、AboveやBeforeなどの関数を利用して、前期との比較や成長率の算出などを行う方法をご紹介しました。