Qlik Senseで着地見込みの計算式を使ったチャート作成

Qlik Senseで着地見込みの計算式を使ったチャートを作成する

着地見込みとは？

Qlik Senseを売上金額の集計に使うようなケースでは、着地見込みが計算できればより便利です。

着地見込みとは月途中の時点で月末の売上がいくらになるかを計算して出された値のことです。

今月の実績から着地見込みを計算する

一般的に、着地見込みは次の数式で計算することができます。

着地見込み　＝　当日までの売上　÷　当日までの経過日数　×　当月の合計日数

「当日までの経過日数」「当月の合計日数」は土日祝日を含めるかどうかで変動しますが、基本は上の考え方で計算します。

サンプルデータ

今回はサンプルデータとして以下のようなファイルを用意いたしました。

①購買データ

②稼働日カレンダー

稼働日カレンダーは土日祝日を対象外にする場合に使用するカレンダーで、2017年～2022年までの祝日データを含んでいます。

(祝日データは2018年1月現在のもので、今後変更される可能性があります)

サンプルデータはこちらからダウンロード

では、はじめましょう！

データの取り込み

Qlik Senseを立ち上げて新規アプリを作成し、データロードエディタで【購買データ】【稼働日カレンダー】を取り込みます。

まずはダウンロードしたデータへの接続を作成します。

ファイルが保存されているフォルダを選び、接続に名前をつけて「作成」をクリックします。

取り込みスクリプトは以下のようにします。

①購買データ

LOAD
伝票番号,
購入日時 as 年月日,
性別,
購入金額,
商品コード
FROM [lib://データフォルダ/購買データ.csv]
(txt, codepage is 932, embedded labels, delimiter is ',', msq);

②稼働日カレンダー

LOAD
年月日,
Date(MakeDate(Year(年月日),Month(年月日),1),'YYYY/MM') as 年月,
Day(年月日) as 日,
曜日,
祝日,
If(休日=1,1,0) as 休日
FROM [lib://データフォルダ/稼働日カレンダー.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

入力できたらデータのロードを行います。

着地見込みチャートの作成

ロードが終了したらチャートと数式の作成をしましょう。

シート編集画面に移り、棒グラフをシートにドラッグ＆ドロップします。

チャートの軸は「年月」に設定します。

メジャーは「当日までの売上」「着地見込み」の2種類を作成します。

それぞれ以下の数式を入力します。

①購入金額実績(当日までの売上)

<span style="color: #6a8fde;">Sum</span>(<span style="color: #cc9900;">購入金額</span>)

②着地見込み

<span style="color: #6a8fde;">Sum</span>(<span style="color: #cc9900;">購入金額</span>) / <span style="color: #6a8fde;">Max</span>({&lt;<span style="color: #cc9900;">購入金額</span>={"&gt;0"}&gt;}日) * <span style="color: #6a8fde;">Day</span>(<span style="color: #6a8fde;">MonthEnd</span>(<span style="color: #6a8fde;">Max</span>(<span style="color: #cc9900;">年月日</span>)))

それぞれの年月で購入履歴のある最後の日を経過日数とみなし、合計日数との割合を計算することで着地見込みを集計します。

当月の合計日数はMonthEnd()関数で月末を取得し、その日を合計日数とします。

この数式では過去の年月の売上と着地見込みは同じ金額になります。

ここまで作成するとチャートは以下のようになっているはずです。

着地見込みのチャートが作成できました。

以上のチャートで計算されているのは土日祝日を含めた着地見込みです。

続いて、土日祝日を稼働日に含めずに着地見込みを計算してみましょう。

土日祝日を集計対象外にする

ここまでの記事では、月途中の購入金額から月末の着地見込みを集計しましたが、今回は着地見込みの集計時に土日祝日を除外したチャートを作成してみましょう。

以下の図はこれまで作成した着地見込みのチャートです。

このチャートを組み替えて土日祝日を除外する仕組みを作ります。

こちらが前回作成した土日祝日を考慮しない着地見込みの数式です。

<span style="color: #6a8fde;">Sum</span>(<span style="color: #cc9900;">購入金額</span>) / <span style="color: #6a8fde;">Max</span>({&lt;<span style="color: #cc9900;">購入金額</span>={"&gt;0"}&gt;}<span style="color: #cc9900;">日</span>) * <span style="color: #6a8fde;">Day</span>(<span style="color: #6a8fde;">MonthEnd</span>(<span style="color: #6a8fde;">Max</span>(<span style="color: #cc9900;">年月日</span>)))

上の数式の「当日までの経過日数」「当月の合計日数」から土日祝日を引いた日数で計算する必要があります。

これをQlik Senseの数式で表現するためにはそれぞれ「土日祝日ではない日数をカウントする」という方法で集計しましょう。

チャートにメジャーを追加し、以下の数式を入力しましょう。

<span style="color: #6a8fde;">Sum</span>(<span style="color: #cc9900;">購入金額</span>) / <span style="color: #6a8fde;">Count</span>({&lt;<span style="color: #cc9900;">購入金額</span>={"&gt;0"},<span style="color: #cc9900;">休日</span>={"0"}&gt;}<span style="color: #cc9900;">年月日</span>) * <span style="color: #6a8fde;">Count</span>({&lt;<span style="color: #cc9900;">休日</span>={"0"}&gt;}<span style="color: #cc9900;">年月日</span>)

数式中の「休日={"0"}」という記述によって土日祝日ではない年月日のみを数え上げることで稼働日のみを集計しました。

メジャーを追加するとチャートはこのようになります。