【Sisense Data Modeling】累計カレンダーで移動年計、Zチャートにチャレンジ！

シャローム！Sato-Gです。
今日はMichaelが熱でダウン！
先週、亀戸ホルモンに一緒に行ったんだけど濃厚接触？？
なーんて、もちろんコロナじゃないよ。
あー、あのとろけるホルモンまた食べたくなっちゃったな。。。

さてさて、前回の【Sisense Data Modeling】累計カレンダーでCOVID-19の移動合計推移を求めてみる
では、累計カレンダーで移動合計を求めたんだけど、今回は売上傾向などを把握する時に利用される移動年計にチャレンジ！
ついでにZチャートも作っちゃうよー
っていう企画です。

1. データの準備

前回の移動合計はCOVID-19のデータを使ったんだけど、１年経っていないから移動年計は出せない。
というか移動年計を出すためには最低でも23ヶ月のデータがないと正確には出せないんだ。
で、今回は【Sisense Data Modeling】データベースの準備～Generic JDBC Connectorで接続のデータを使うことにする。
ElastiCubeのファイルはここに入ってるので必要な方はダウンロードしてね。

2. 累計カレンダーの作成

前回の記事では日単位の累計だったけど、移動年計は月単位で集計する。だから累計カレンダーも月単位となる。
まずは元々のデータモデルの確認。

2.1 カレンダーテーブルの作成

まずはカレンダーテーブルを作成する。[カレンダー]テーブルは[明細]テーブルの年月日の期間分作成することになるので、データ件数の多い[明細]テーブルを利用してカレンダーテーブルを以下のSQLで作成する。
一応、歯抜け対策も含めて。

SELECT  年月日_Min + (rownum-1) *(60*60*24) AS 年月日,
        ToString(GetYear(年月日_Min + (rownum-1) *(60*60*24))) + '-' + Right('0'+ToString(GetMonth(年月日_Min + (rownum-1) *(60*60*24))),2) AS 年月
FROM
(SELECT Min(年月日) AS 年月日_Min,
        Max(年月日) AS 年月日_Max
FROM 明細) t1,
(SELECT
        RANKASC(年月日) AS rownum
FROM 明細
)  t2
WHERE 年月日_Min + (rownum-1) *(60*60*24) <= 年月日_Max

このテーブルはDate型の年月日とText型の年月（yyyy-MM）の２カラム構成のテーブルになる。

2.2 累計カレンダーテーブルの作成

次は累計カレンダーの作成に移る。
SQLは以下のとおり。

SELECT  ToInt(StrParts(d1.年月,'-', 1))     AS 累計年,
            d1.年月     AS 累計年月,
            d2.年月     AS 年月,
            MonthDiff(d1.年月_date,d2.年月_date) AS Counter
FROM
(SELECT  DISTINCT 年月,
        CreateDate(
            ToInt(StrParts([年月],'-', 1)),
            ToInt(StrParts([年月],'-', 2)),
            1
        ) AS 年月_date
FROM カレンダー
) d1,
(SELECT  DISTINCT 年月,
        CreateDate(
            ToInt(StrParts([年月],'-', 1)),
            ToInt(StrParts([年月],'-', 2)),
            1
        ) AS 年月_date
FROM カレンダー
) d2
WHERE d1.年月 >= d2.年月

ちょっと解説しよう。
d1とd2という２つのテーブルを以下のSQLで作成する。
年月は'yyyy-MM'形式のテキストになるので、月の差を求めやすいようにCreateDate関数を使用してDate型にしておく。
年月の'2020-01'は’2020/01/01'のようにその月の初日が[年月_date]となる。

SELECT  DISTINCT 年月,
        CreateDate(
            ToInt(StrParts([年月],'-', 1)),
            ToInt(StrParts([年月],'-', 2)),
            1
        ) AS 年月_date
FROM カレンダー

d1とd2は同一のSQLなので自己結合に等しい。
FROM句で、FROM d1,d2とすることで、d1×d2の組み合わせテーブルが作成される。
d1.年月が[累計年月]となり、d2.年月がカレンダーテーブルに紐づく[年月]となるが、WHERE d1.年月 >= d2.年月として、年月は累計年月以前の月だけになるように制限する。
そしてMonthDiff関数を用いて、基準となる[累計年月]と累計される年月である[年月]の差を求める（Counter）。

SELECT  ToInt(StrParts(d1.年月,'-', 1))     AS 累計年,
            d1.年月     AS 累計年月,
            d2.年月     AS 年月,
            MonthDiff(d1.年月_date,d2.年月_date) AS Counter
FROM　d1,d2
WHERE d1.年月 >= d2.年月

2.3 リレーションシップ設定

テーブルができあがったので、以下のリレーションシップを設定する。
明細.年月日 - カレンダー.年月日
カレンダー.年月 - 累計カレンダー.年月

3. ウィジェットの作成

3.1 累計表示の棒グラフ

まずシンプルに[カテゴリ]に[累計年月]、数式にSUM(注文金額)を設定して棒グラフを作成してみる。
各累計年月は累計年月以前のすべての年月を持っているので、全ての累計が表示されることになる。

累計表示というとRSUM関数を想像すると思うけど、RSUM関数はウィジェットに表示されている期間の累計にしかならない。
しかし、累計カレンダーを使うと、フィルターで表示期間が絞り込まれてもきちんと累計計算が行われる。

2020年1月以降でフィルター指定しても、この通り表示期間前の累計が計算されて表示される。

3.2 移動年計の棒グラフ

移動年計は通常は12ヶ月の累計だが、ここではパラメータ化したい。
値は以下のように設定する

■値１（移動年計）
SUM(注文金額)
※ダッシュボードフィルタでパラメータ指定する。
※「系列の種類」はエリアに設定する。

■値２（当月）
(SUM(注文金額),[Counter])
※Counterはフィルタ設定で0（当月）で絞り込んでおく。

ダッシュボードフィルターに累計年月とCounterを配置し、Counterを「<=11」と設定する。
これで12ヶ月の累計（つまり移動年計）が計算されることになる。
結果は以下のとおり。

留意したいのは最初の11ヶ月は累計できる月数が不足しているので正しくないということ。
12ヶ月目からは正しく年計が計算され、右肩上がりで金額が順調に伸びていることがわかる。
こんなふうに当月だけでは明確に金額が増加しているか判断がつかないデータでも、移動年計を用いると季節変動を平準化した傾向が把握できる。

3.3 Zチャート

せっかく移動年計ができるようになったので、ついでにZチャートを作ってしまおう。
Zチャートは折れ線グラフを使って表現する。
上記で作成した移動年計の棒グラフを折れ線グラフにして以下の「値」を追加する。

■値３（累計）
RSUM((SUM(注文金額),[Counter]))
※Counterはフィルタ設定で0（当月）で絞り込んでおく。

さらに、ダッシュボードフィルターに累計年を追加し「2020」を選択すると下記のようなZチャートが出来上がる。
簡単でしょ？

2019年のZチャートは以下のようになる。

Zチャートは2019年も2020年も年末に向けて売上が上昇する成長型という意味では共通しているが、2019年のほうが移動年計の傾きが大きく、2020年の売上基盤には2019年の売上増が寄与していることがわかる。

4. まとめ

今回は累計カレンダーを使用して移動年計チャートやZチャートを作成してみた。
実を言うと、この方法は他のBIでも使えるワザであって、Sisense特有のものではない。
発想の転換だよね。

以上で累計カレンダーの紹介はおしまい。
次は何のネタにしようかな...

ではまた！