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

こんにちは、Yokoです。
昨日は、中野セントラルパークでBBQしてきました。
夏はBBQとビールだよね。
炭火だともっといいんだけど、中野セントラルパークじゃあ無理か😅

さて、Qlikの日付処理、まだまだ続きます。
よくネタあるよね😄

契約者数を求める…という課題

こんなデータがあるとして、このデータを使って契約者数の推移を求めなさいと言われたは、どうしますか？
こういうFrom To のデータからは、契約を開始した人、契約を終了した人の推移は簡単に出せますが、その時点で何人の契約者がいたのかは、時間軸になる項目がないのだから出せませんーということになる。
その項目を作れと言われれば、やれないことはないけど、レコード数が膨大になるかもしれないよね。
そんな時、レコード数を増やさずに契約者数の推移を視覚化する方法をご紹介します。

期間を選択するカレンダーテーブルを作成する

期間を指定しなければならないので、期間を選択するためのカレンダーテーブルを作成する必要があります。
データを見ると、契約が終了していない人は、契約終了日に”2999/12/31″という値が入っているようなので、その値を除いた年月日の始点と終点を求めます。
そして、AutoGenerateを使用して、カレンダーテーブルを作成します。

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');

Drop Tables tmp,tmp2;

calendar:
Load 
	*,
    Year(年月日) as 年,
    Month(年月日) as 月,
    Date(MonthStart(年月日),'YYYY/MM') as 年月
    ;
Load 
	Date('$(varMinDate)' + IterNo() -1) as 年月日
AutoGenerate 1
While '$(varMinDate)' + IterNo() -1 <= '$(varMaxDate)';

はい、こんなデータモデルになりました。
でも、これってcalendarテーブルがcustomerテーブルとつながっていないアイランドテーブルだから、紐づかないじゃないかって思いますよね。
でも、それでいいの。そのうちわかるから…

変数の準備

チャートを作成する前に、期間の始点、終点を指定する変数を作っておきます。

変数名	定義
vMinDate	=Min(年月日)
vMaxDate	=Max(年月日)

これらの関数を元に、期間における契約者数を求める基本的な数式を考えてみましょう。

契約開始日が終点以前である
契約終了日が始点以降である

これを数式で表すと、こうなりますね。

=Count({$<契約開始日={"<=$(vMaxDate)"},契約終了日={">=$(vMinDate)"}>} Distinct 会員番号)

今回ご紹介するソリューションでは約束事があります。
それはチャートで表示する年月の最大表示期間です。
別に100ヶ月だろうが、可能と言えば可能なのですが、通常は年度で見ることが多いと思いますので、今回は12ヶ月ということにしておきます。

指定された期間で最大12ヶ月表示するために、次の２つの変数を準備しておきます。

変数名	定義
vConcat_YM	=Concat(Distinct {$<年月日={“>=$(=MonthStart(AddMonths(‘$(vMaxDate)’,-11)))<=$(vMaxDate)”}>} 年月,Chr(39) & ‘,’ & Chr(39), 年月)
vMonthsCnt	=Count(Distinct {$<年月日={“>=$(=MonthStart(AddMonths(‘$(vMaxDate)’,-11)))<=$(vMaxDate)”}>} 年月)

vConcat_YMでは、選択された期間の年月がカンマ区切りで結合されて表示されます。最後の引数で[年月]を指定しているのは年月順でソートして結合させるためです。
数式ではAddMonths(年月,-11)以降という指定になっていますので、終点が2026/12であれば2026/01′,’2026/02′,’2026/03’…’2026/12 というような結果が返ってきます。
（-11ではなく-23とすれば2年分を表示できます）
vMonthsCntでは、指定された期間の月数を集計します。

パラメータ付き変数で、数式を定義する

ベースとなる数式を変数で定義します。
なぜ、数式を変数で定義するのかというと、長い数式を書くのが面倒くさいというのもありますが、メンテナンス性が良いというのが最大の理由です。
そして、$マジックでパラメータ指定することで、さらにシンプルにします。
パラメータ付き変数については、こことここの記事で解説していますので、参考にしてね。

変数名	定義
vMeasure	Count({$< 契約開始日={“<=$(=MonthEnd(AddMonths(‘$(vMaxDate)’,-$(vMonthsCnt)+$1)))”}, 契約終了日={“>=$(=MonthStart(AddMonths(‘$(vMaxDate)’,-$(vMonthsCnt)+$1)))”} >} Distinct 会員番号)

あ、パラメータ付きの変数では数式に頭に”=”は絶対につけないで下さいね。
つけたら、動きません😆

チャートの作成

シートには予め、年、月、年月のフィルターを追加しておきます。
今回作成するチャートは軸が年月、メジャーが契約者数です。
軸は以下のようにValueListで作成します。

■軸の指定

=ValueList('$(vConcat_YM)')

これで、指定された期間の最大12ヶ月の年月をリストするようになります。ラベルは「年月」としておきましょう。

■メジャーの数式

=Pick(Match(ValueList('$(vConcat_YM)'),'$(vConcat_YM)'),
$(vMeasure(1)),
$(vMeasure(2)),
$(vMeasure(3)),
$(vMeasure(4)),
$(vMeasure(5)),
$(vMeasure(6)),
$(vMeasure(7)),
$(vMeasure(8)),
$(vMeasure(9)),
$(vMeasure(10)),
$(vMeasure(11)),
$(vMeasure(12))
)

Pick(Match()…)を使って、ValueListで指定された年月に対応した数式を列記します。
ねっ！これ、12個の数式を書くとなると面倒くさいでしょ？
なので、パラメータ付きの変数を使ったんですよ。もし数式が間違えていたら大変ですからねー

チャートの確認

できあがったチャートを見てみます。
2026年を選択すると、こんな感じです。

2026年4月から6月を選択すると、こうなります。
ちゃんと指定期間で表示されていますね。

ついでにコンボチャートにして前年同月と比較できるようにしましょう。
前年の数式はこちらになります。

=Pick(Match(ValueList('$(vConcat_YM)'),'$(vConcat_YM)'),
$(vMeasure(-11)),
$(vMeasure(-10)),
$(vMeasure(-9)),
$(vMeasure(-8)),
$(vMeasure(-7)),
$(vMeasure(-6)),
$(vMeasure(-5)),
$(vMeasure(-4)),
$(vMeasure(-3)),
$(vMeasure(-2)),
$(vMeasure(-1)),
$(vMeasure(0))
)

はい、前年比較推移のチャートができました。
パラメータ付き変数にしていなかったら大変なことになってました😄