はじめに

データ分析を行う場合には年、半期、四半期、月といった切り口で分析を行うためにマスターカレンダーの作成が必要となります。ここではテンプレートとして利用可能なマスターカレンダー作成を行うスクリプトをご紹介します。

image

また、場合によっては分析対象のデータに日付のフォーマットとしてデータが保存されていない場合があります。作成するマスターカレンダーとは日付をキー項目として関連付ける必要がありますので、そのような場合に対応するための日付のフォーマット変換についても併せてご説明します。

事前の準備

このエントリでは、「10分で試すQLIK SENSEでデータ分析」の手順に従ってQlik Sense Desktopのインストールとアプリの作成(「売上分析」アプリ)が完了していることを前提に作業を進めます。もし作業をまだ実施されていなければ、以下のリンク先の手順に従って作業を実施して下さい。

>>「10分で試すQLIK SENSEでデータ分析」

マスターカレンダー

標準マスターカレンダーの作成

左上のナビゲーションアイコンから「データロードエディタ」を新規タブで表示します。

image

「自動生成セクション」のタブを選択し、「+」ボタンを押してロードスクリプトのタブを追加します。

image

タブの名前を「マスターカレンダー」に変更します。

image

以下のスクリプトを入力します。以下赤字の「販売日付」が元データの日付を格納する項目、「売上明細」はその項目を格納しているQlik Sense上のテーブル名となっています。以下のスクリプトを他でも利用する場合、環境に合わせてこれらの名称の修正が必要です。


MinMax:
LOAD Max(販売日付) AS MaxDate,
	 Min(販売日付) AS MinDate
RESIDENT 売上明細;

LET varMinDate = Num(Peek('MinDate',0,'MinMax'));
LET varMaxDate = Num(Peek('MaxDate',0,'MinMax'));

DROP TABLE MinMax;

TempCalendar:
LOAD
DATE($(varMinDate) + ROWNO() -1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) +1;

マスターカレンダー:
LOAD
	DATE(TempDate) AS 販売日付,
	WEEK(TempDate) AS 週,
	YEAR(TempDate) AS 年,
	MONTH(TempDate) AS 月,
	DAY(TempDate) AS 日,
	DATE(MONTHSTART(TempDate), 'YYYYMM') AS 年月,
       If(CEIL(MONTH(TempDate)/6)=1,'上半期','下半期') AS 半期,
	'第' & CEIL(MONTH(TempDate)/3) & '四半期' AS 四半期,
	WEEKDAY(TempDate) AS 曜日
RESIDENT
TempCalendar
Order BY
TempDate ASC;

DROP FIELD TempDate;

「データのロード」を実行します。

image

左上のナビゲーションアイコンから「データモデルビューア」を開きます。

image

「売上明細」の「販売日付」項目とキーとして紐付いた「マスターカレンダー」テーブルが追加されていることが確認できます。

image

データのプレビューを確認すると、以下の形でデータが格納されていることが確認できます。

image

会計年度のカレンダー作成

以下の例では、上記のマスターカレンダー作成の処理に、4月を期首月とした会計年度のカレンダーを追加する処理を加えています(赤字部分を追加)。期首月は「vFM」の変数で設定されており、変数の値を変更することで期首月の変更が可能です。尚、この例では4月を期首月とした場合、2015年4月は2015年度とする計算となっています。


MinMax:
LOAD Max(販売日付) AS MaxDate,
	 Min(販売日付) AS MinDate
RESIDENT 売上明細;

LET varMinDate = Num(Peek('MinDate',0,'MinMax'));
LET varMaxDate = Num(Peek('MaxDate',0,'MinMax'));

DROP TABLE MinMax;

TempCalendar:
LOAD
DATE($(varMinDate) + ROWNO() -1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) +1;

Set vFM = 4 ;  //期首月

マスターカレンダー:  
Load
    	If(CEIL(会計月/6)=1,Dual('上半期',1),Dual('下半期',2)) AS 会計半期,
	'第' & CEIL(会計月/3) & '四半期' AS 会計四半期,
    	  *;
Load 
	年 + If(月>=$(vFM), 0, -1) as 会計年度,           
        Dual(月, Mod(月-$(vFM), 12)+1) as 会計月,
          *;
LOAD
	DATE(TempDate) AS 販売日付,
	WEEK(TempDate) AS 週,
	YEAR(TempDate) AS 年,
	MONTH(TempDate) AS 月,
	DAY(TempDate) AS 日,
	DATE(MONTHSTART(TempDate), 'YYYYMM') AS 年月,
    If(CEIL(MONTH(TempDate)/6)=1,Dual('上半期',1),Dual('下半期',2)) AS 半期,
	'第' & CEIL(MONTH(TempDate)/3) & '四半期' AS 四半期,
	WEEKDAY(TempDate) AS 曜日
RESIDENT
TempCalendar
Order BY
TempDate ASC;

DROP FIELD TempDate;

日付データのフォーマット変換

上記の例では売上データの「販売日付」は正しい日付フォーマットとなっていたため、この「販売日付」を元にマスターカレンダーを作成することができました。ただ、場合によっては分析対象のデータが日付のフォーマットとなっていないため、上記の方法が利用できなくなってしまいます。そのような場合にはロードスクリプトでフォーマット変換などの処理を行う必要があります。以下はそのアプローチについてご説明します。

文字列で日付が格納されている場合

「20150101」といった形式で格納されている場合等がこれに該当します。

まず、混同されやすいのですが、Qlik Senseには大きく分けて以下の2種類のDate関数があります。

  • Date#()関数 : 文字列を入力として、日付として評価できるように変換
  • Date()関数 : 日付のデータを入力として、日付の表示フォーマットを変換

Data#()は文字列から日付への変換であるのに対し、Date()は日付から日付のフォーマット変換であるところがポイントです。「20150101」といった文字列を日付として扱えるようにするには「Date#()」を利用します。


Date#(販売日付,'YYYYMMDD') AS 販売日付

2つめの引数は「販売日付」が’YYYYMMDD’のフォーマットであることを明示しています。これで日付として扱えるようになりますが、表示フォーマットは「20150101」となったままになりますので、その変換後のデータを入力としてDate関数を使って表示フォーマットを変更することが出来ます。

Date(Date#(販売日付,'YYYYMMDD'),'YYYY/MM/DD') AS 販売日付 

上記の例では「2015/01/01」の表示フォーマットに変更しています。

タイムスタンプの格納されている場合

「2013/12/12 12:11:52」といったタイムスタンプで格納されている場合等がこれに該当します。タイムスタンプのデータはQlik Sense内部では整数部が日付、小数点以下が時間の数値で表現されており、Floor関数で小数点以下を切り捨てることで日付のみを取り出せます。

Date(Floor(販売日付),'YYYY/MM/DD') AS 販売日付

上記の例ではFloor関数で日付を取り出し、Date関数で「2015/01/01」の表示フォーマットに変更しています。尚、上記で切り捨てた時間の部分は逆に整数部分のみを取り出すFrac関数を使って以下で取り出すことが出来ます。

Time(Frac(販売日付),'hh:mm:ss') AS 販売時間

年、月、日が別々の列に格納されている場合

「2015」、「1」、「1」といった形で日付が格納されている場合等がこれに該当します。その場合にはこの3つのデータを入力として日付を作成できるMakeDate関数が利用できます。

MakeDate(年,月,日) AS 販売日付

まとめ

以上、マスターカレンダーの作成方法と日付のフォーマット変換についてご説明しました。