Qlikの日付処理のベストプラクティス〜第9回 From Toデータの扱いその2〜

こんにちは！Yokoです。
先週金曜日にBBQをやったばかりなのですが、今週の金曜日も会食があり、僕がお店を決めるのでどこにしょうかなあ〜と…
BBQは牛、豚、鶏でした。
食べていないのは羊ですねえ。
武蔵小杉に「孤独のグルメ」に出ていた、おいしそうなジンギスカン屋さんがあったことを思い出し、そこにしようと思って予約入れようとしたところで、相手の方がビール飲めない人だった！と思い出し、普通のお店にしてしまいました。
焼肉にはビール！
っていうのは僕の勝手な思い込みでしょうか😅

さて、From Toデータの扱いその1では、Pick(Match…を使って契約者数を出してみましたが、今回は別のアプローチで攻めたいと思います。
それは、契約開始日と契約終了日しかないのなら、契約年月の「データを生成する」という方法です。
では、さっそく解説して行きましょう！

From Toデータから新たにデータを発生させる意味

Qlikで時系列で推移を見るようなチャートを作成する時に、軸には時間軸を設定します。
しかし、契約開始日と契約終了日しか時間に関する項目がないのであれば、各月に相当する時間軸の項目がありません。
じゃあ、作っちゃえっていうことです。
つまり、2026年4月に契約開始、2026年7月契約終了であれば、以下のようなテーブルを作成してしまえばいい。

会員番号	年月
10	2026/04
10	2026/05
10	2026/06
10	2026/07

こういうデータなら、軸に契約年月、メジャーにCount(Distinct 会員番号)を設定すれば簡単にできますよね！
一点、懸念点があるとすれば、このデータを作成するとなると、データが多すぎるのでないかということ。
例えば、100万人の会員がいて平均50ヵ月間契約していたとすると、5000万レコードか…
これでパフォーマンスでるのかなあ。
まずは、やってみますか…

From Toのデータから各月のデータを作成するスクリプト

1. 変数の準備

まずは、レコードを生成するために変数を準備します。
作成する変数は3つ

varMinDate：契約開始日の最小
varMaxDate：契約終了日の最大（契約終了が決まっていない場合の日付を除く）
varMonthsCnt：varMinDate〜varMaxDateまでの月数

作成するスクリプトはこちら

//①契約期間のMin, Max, 最大の期間
tmp:
Load 
	Date(Min(契約開始日)) as MinDate
Resident customer;

tmp2:
Load 
    Date(Max(契約終了日)) as MaxDate
Resident customer
Where 契約終了日 <> '2999/12/31';	//契約終了が決まっていない場合を除く

Let varMinDate = Peek('MinDate',0,'tmp');
Let varMaxDate = Peek('MaxDate',0,'tmp2');
Let varMonthsCnt = Year('$(varMaxDate)') * 12 + Month('$(varMaxDate)') - (Year('$(varMinDate)') * 12 + Month('$(varMinDate)')) + 1;

Drop Tables tmp,tmp2;

varMonthsCntで月数を求める方法ですが、そんな関数はないし、日数の差を12で割るというのも正確ではないので、

(Year(最大日) * 12 + Month(最大日)) - (Year(最小日) * 12 + Month(最小日)) + 1

という数式で求めます。
※ +1しないと当月は0になってしまいますので、月数を正確に求めるためには差を求めた上で、+1する必要があります。

2. 月数パターンテーブルの作成

月数パターンテーブルを作成します。
このテーブルを作成する目的ですが、期間が0ヵ月であれば1レコード（0ヵ月後）、1ヵ月であれば2レコード（0ヵ月後, 1ヵ月後）…というようなパターンを最大の期間分作成するということです。

//②月数パターンのテーブルを作成
For i = 0 to varMonthsCnt - 1

  months:
  Load 
      $(i) as months_total,
      IterNo() -1 as months_cnt
  AutoGenerate 1
  While IterNo() -1 <= $(i);

Next i

このスクリプトで作成されるmonthsテーブルは以下のとおり。
これで、このテーブルを作成する理由がわかった人はかなり勘が鋭いです。

3. 契約テーブルを作成する

customerテーブルから契約テーブル(contract_tmp)を作成します。
ここでは契約開始〜終了までの月数をmonths_totalとして求めています。
+1しない理由は、当月の場合は0にしておきたいんですよね。
ここで、理解できた人は、まあまあ勘がいいです。

//③契約テーブル
contract_tmp:
Load 
	会員番号,
    契約開始日,
    契約終了日,
    Year(契約終了日) * 12 + Month(契約終了日) - (Year(契約開始日) * 12 + Month(契約開始日)) as months_total;
Load 
	会員番号,
	契約開始日,
    If(契約終了日 > '$(varMaxDate)', '$(varMaxDate)',契約終了日) as 契約終了日
Resident customer;

4. 月数パターンをJoinする

契約テーブル（contract_tmp）に月数パターンテーブル（months）をJoinします。
このJoinは何を意味するのか…
契約期間が3ヵ月ある会員には3レコードがJoinされ、それぞれに、0, 1, 2というカウンタ（months_cnt）がつく…という仕掛けです。
ここまでくると大半の人は理解したと思います。

スクリプトは以下のとおり

//④月数パターンをJoin
Left Join(contract_tmp)
Load *
Resident months;

5. 契約テーブルを確定させる

月数のカウンターがついたレコードが生成されたので、契約テーブル（contract）を確定させます。
このスクリプトで、会員単位で契約月数分のレコードが生成されます。
そして、不要になったテンポラリーテーブルはDropしておきます。

//⑤契約テーブル確定
contract:
Load 
	会員番号,
    AddMonths(契約開始日, months_cnt) as 年月日
Resident contract_tmp;

Drop Table months,contract_tmp;

6. カレンダーテーブルを作成

契約テーブルはあくまで日付のデータでした。
仮に契約開始日が 2026/04/10だとすると、AddMonths(契約開始日, 1)は 2026/05/10です。
年月単位で契約者数を求めたいので、カレンダーテーブルを作成します。
ここでは、選択用に年、月、年月を生成します。

//⑥カレンダーテーブル
calendar:
Load 
	*,
    Year(年月日) as 年,
    Month(年月日) as 月,
    Date(MonthStart(年月日),'YYYY/MM') as 年月
    ;
Load 
	Date('$(varMinDate)' + IterNo() -1) as 年月日
AutoGenerate 1
While '$(varMinDate)' + IterNo() -1 <= '$(varMaxDate)';

データモデルはこうなります。契約テーブルは3700万件を超えてしまいました😧

チャートで確認

データモデルができたので、チャートを作成してみます。
ここでは面倒な数式を書かずに、軸は年月、メジャーはCount(Distinct 会員番号)としてみました。

気になるレスポンスですが、1秒くらいで返ってきました！
Qlik速いですねえ。
チャートからわかることは、2024年の半ばを境に、会員数は減少傾向になり2027年1月以降さらに下降傾向に拍車が掛かっているようにみえる。
前回ご紹介した、Pick(Match..で作成したチャートは12ヵ月表示にしていましたので、全体の傾向が見えにくくなっていましたが、こちらは何の制約もないので使い勝手が良いですね。
しかも思ったよりパフォーマンスは良い！
2026年の前年対比もやってみましたが、月別に見ても前年よりかなり会員数が減っているのがわかります。