Qlikの日付処理のベストプラクティス〜第7回月のソート自由自在〜

こんにちは！Yokoです。
このところ、業務多忙により、ブログ投稿をサボっていました😅
日付処理のベストプラクティスも続きが全然掛けていませんでした🙇‍♂️

ということで、今回は猛省して、日付処理のベストプラクティスの7回目の投稿とします。
以前、Qlikの日付処理のベストプラクティス〜第3回前年比較の表現〜という記事で、前年比較の方法をご紹介したのですが、月単位で売上の前年比較を行う場合、「年度またぎ」という壁が立ちはだかります。
時々、前年比較は会計年度で行うことが多いので、「またぐ必要はないでしょ」という前提で作りますが、「またぎたいユーザの気持ちもわからないでもない」というのが本心ですよね。
できるものならやってますーというところです。
今回は、その「わからないでもない」本心を実現していきますよ。

年度またぎができない理由

Qlikの日付処理のベストプラクティス〜第3回前年比較の表現〜では、AutoCalendarで実装しましたが、年度またぎ問題にフォーカスして解説したいので、Master Calendarを作成して日付を指定できるようにしておきます。
Master Calendarは以下のスクリプトで作成しました。

Let vFYStartMonth = 4;

MasterCalendar:
Load 
	Distinct
    売上日,
    Year(売上日) as 年,
    Dual(Year(Addmonths(売上日,1-$(vFYStartMonth))),YearStart(売上日,0,$(vFYStartMonth))) as 会計年,
    Month(売上日) as 月,
    Dual(Month(売上日),
    	If(Month(売上日)>=$(vFYStartMonth),Month(売上日)-$(vFYStartMonth)+1,
        	Month(売上日)-$(vFYStartMonth)+13)) AS 会計月,
    Date(MonthStart(売上日),'YYYY/MM') as 年月
Resident 売上;

このMaster Calendarでは会計年度の開始を4月に設定しています。
よって、会計月はDual関数を使って、4月から翌年の3月でソートされるように設定しました。
Dual関数では
Dual(4月,1), Dual(5月,2), Dual(6月,3),…Dual(3月,12)
のように設定されますので、2025年3月から2025年5月を選択した場合は、4月→5月→3月と並んでしまいますよね。
そう、ここでソート順を定義しているのが原因なんです。
だから、そのソート順の定義とは異なる定義を、チャート上でしてあげればいいということなんです。
では、早速トライアルしてみましょう。

対策前の仕様

ごく一般的なダッシュボードを作成してみます。
上部のフィルターは、会計年、会計月、年月で構成して、コンボチャートはチャートは以下のように設定しました。

設定項目	設定
軸	会計月
メジャー（当期）	Sum({$<売上日={“>=$(vMinDate)<=$(vMaxDate)”}>} [売上金額])
メジャー（前期）	Sum({$<売上日={“>=$(vLast_MinDate)<=$(vLast_MaxDate)”},会計年,会計月,年月>} [売上金額])

変数は以下の設定です。

変数名	定義
vMinDate	=Date(Min(売上日))
vMaxDate	=Date(Max(売上日))
vLast_MinDate	=AddYears(‘$(vMinDate)’,-1)
vLast_MaxDate	=AddYears(‘$(vMaxDate)’,-1)

以上で、基本形はできましたので、選択して結果を見てみます。

2025年11月から2026年2月までを選択（左）
2025年2月から2025年5月までを選択（右）

はい、年度をまたいでいる「2025年2月から2025年5月」では月のソート順が崩れてしまいました。

【対策1】変数と数式で正しいソート順にする

上記の設定では、月のみでソートしようとしていることが原因でした。
では、そこに年月を加味してソート順を設定すれば問題ないのではないか？
と思いません？
ではやってみますね。
まずは、ソート順を定義するための変数を作成します。変数名はvMonthOrderとします。

=Concat({$<売上日={">=$(vMinDate)<=$(vMaxDate)"}>} Distinct 年月,Chr(39) & ',' & Chr(39),年月)

この変数では、年月をConcatするのに、ソート順として年月を設定しました。
よって、2025年2月から2025年5月を選択した状態だと、
　2025/02′,’2025/03′,’2025/04′,’2025/05
という文字列が得られます。
じゃあ、この文字列を使ってソート順を設定できるんじゃないの？
というのが、１つ目のアイディアです。

ソートの設定で、月のソートをカスタム設定にして、「昇順」で以下のように数式を設定します。

=Pick(Match(Aggr(Only({<売上日={">=$(vMinDate)<=$(vMaxDate)"}>} 年月),年月),
 '$(vMonthOrder)'),
 1,2,3,4,5,6,7,8,9,10,11,12)

この数式のロジックは、Pick(Match…)を使って、
今年度の売上年月をConcatした文字列（ソート）と照合して、合致した場合は1〜12の値を返す
というものです。
結果は、以下のとおり！
想定した順番に月が並んで表示されるようになりました。

【対策2】CalendarでDualの年月を作成しておく

上で紹介した「月」のソートは、一般的なQlikのユーザには確かに有効です。
Tableauとか、Google Analyticsみたいに月じゃなくて年月表示で前年比較はできないの？
と思う人はきっといると思うんですよね…
僕はTableauも使っていたことがあるので、「前年比較がサクッとできていいなあ」しかも「年月で表示できてる」って思っていました。
Qlikではこんなことできないのでは？と思っている、そこのあなた！
できますよ！！
いや、実はめっちゃ簡単です。

Master Calendarで以下のように、年月_dualというカラムを作成しておきます。

MasterCalendar:
//内部値に月を持つ年月を作成
Load *,
	Dual(年月,月) as 年月_dual;
Load 
	Distinct
    売上日,
    Year(売上日) as 年,
    Dual(Year(Addmonths(売上日,1-$(vFYStartMonth))),YearStart(売上日,0,$(vFYStartMonth))) as 会計年,
    Month(売上日) as 月,
    Dual(Month(売上日),
    	If(Month(売上日)>=$(vFYStartMonth),Month(売上日)-$(vFYStartMonth)+1,
        	Month(売上日)-$(vFYStartMonth)+13)) AS 会計月,
    Date(MonthStart(売上日),'YYYY/MM') as 年月
Resident 売上;

そして軸は、「月」ではなく「年月_dual」を設定します。
ソート順は、カスタムにして「年月」を指定すればOK。

簡単だったでしょ？
今回は、Master Calendarでやってみましたが、AutoCalendarでもできますよね。
Qlikの日付処理のベストプラクティス〜第1回 Auto Calendar〜のスクリプトに以下を追加すれば今回と同じように実装できます（以下の例は、fiscal側の設定です）。

    Dual(Year($1) &'-'& Month($1),
    	If(Month($1)>=$(vFYStartMonth),Month($1)-$(vFYStartMonth)+1,
        	Month($1)-$(vFYStartMonth)+13)) AS [YearMonth_dual] Tagged ('$axis','$yearmonth_dual','$cyclic'),

まとめ

月のソート順をフレキシブルに設定する方法をご紹介しました。
そして、おまけとして、月ではなく年月で、前年比較のチャートを実現する方法も追加しておきました。

ポイントは

月のソートは当年の期間の年月でソートする

ということです。

補足として、今回ご紹介した方法は12ヶ月を超える期間を指定した場合は破綻しますので、演算実行条件に以下のような設定を施しておいたほうがベターです。

Count({<売上日={">=$(vMinDate)<=$(vMaxDate)"}>} Distinct 年月) <= 12

以上、月のソート設定でやってみましたが、01/01〜12/31というような月日でも同じ方法でできそうですよね。
僕はやったことありませんが、誰かチャレンジしてみて下さい😄
ではまた！

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！

Qlik パーシャルリロードのエラーの対処法

この記事を書いた人

Yoko

Qlikの日付処理のベストプラクティス〜第7回 月のソート自由自在〜