Qlikの日付処理のベクスプラクティス〜第1回 Auto Calendar〜

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='¥#,##0;-¥#,##0';
SET TimeFormat='h:mm:ss';
SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY/MM/DD h:mm:ss[.fff]';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='ja-JP';
SET CreateSearchIndexOnReload=1;
SET MonthNames='1月;2月;3月;4月;5月;6月;7月;8月;9月;10月;11月;12月';
SET LongMonthNames='1月;2月;3月;4月;5月;6月;7月;8月;9月;10月;11月;12月';
SET DayNames='月;火;水;木;金;土;日';
SET LongDayNames='月曜日;火曜日;水曜日;木曜日;金曜日;土曜日;日曜日';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

売上:
LOAD
    売上日,
    店舗ID,
    顧客ID,
    伝票番号,
    商品コード,
    数量,
    売上金額,
    原価
FROM [lib://<path>/売上_2024.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

LOAD
    売上日,
    店舗ID,
    顧客ID,
    伝票番号,
    商品コード,
    数量,
    売上金額,
    原価
FROM [lib://<path>/売上_2025.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);


商品:
LOAD
    商品コード,
    カテゴリコード,
    商品名,
    カテゴリ名,
    単価
FROM [lib://<path>/商品.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

顧客:
LOAD
    顧客ID,
    性別,
    生年月日,
    都道府県,
    市区町村,
    登録日,
    有効フラグ
FROM [lib://<path>/顧客.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

店舗:
LOAD
    店舗ID,
    店舗名,
    店舗タイプ,
    店舗都道府県,
    店舗市区町村
FROM [lib://<path>/店舗.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

//会計年度の開始月
Let vFYStartMonth = 4;

//会計年度対応
[fiscal]: 
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
	Dual(Year(Addmonths($1,1-$(vFYStartMonth))),YearStart($1,0,$(vFYStartMonth)))  AS [Year] Tagged ('$axis', '$year'),
    'Q' & Ceil(Month(Addmonths($1,1-ADD$(vFYStartMonth)))/3) AS [Quarter] Tagged ('$axis','$quarter','$cyclic'),
	Dual(Year(Addmonths($1,1-$(vFYStartMonth))) & '-Q' & Ceil(Month(Addmonths($1,1-$(vFYStartMonth)))/3),QuarterStart($1,0,$(vFYStartMonth))) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),
    Dual(Month($1),
    	If(Month($1)>=$(vFYStartMonth),Month($1)-$(vFYStartMonth)+1,
        	Month($1)-$(vFYStartMonth)+13)) AS [Month] Tagged ('$axis','$month','$cyclic'),      	
 	Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
    Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
	Dual(
		'W' & Num(Week($1,0),'00')
		,If(
			 Month($1)>=$(vFYStartMonth)
			,Week($1,0)-Week(YearStart($1,$(vFYStartMonth)),0)+1	
			,Week($1,0)+Week(YearEnd($1,13+$(vFYStartMonth)),0)+1
		)
	) AS [Week] Tagged ('$axis','$weeknumber','$cyclic'),
	Date(WeekStart(Addmonths($1,0),0,0)) AS [WeekStart] Tagged ('$axis','$weekstart','$cyclic'),
	Year($1) & 'W' & Num(Week($1,0),'00') AS [YearWeek] Tagged ('$axis','$yearweek','$qualified'),
	WeekDay($1) AS [Weekday] Tagged ('$axis','$weekday','$cyclic'),
    Dual(Num(Month($1),'00') & '/' & Num(Day($1),'00'),
    	Month(Addmonths($1,1-$(vFYStartMonth)))*100+Day(Addmonths($1,0))) AS [MonthDay] Tagged ('$axis','$monthday','$cyclic'),
    Date(Floor($1)) AS [Date] Tagged ('$axis', '$date','$qualified'),
    Day($1) + weekday(MonthStart($1)) As [CalendarPosition] Tagged ('$axis','$weekday','$cyclic');
DERIVE FIELDS FROM FIELDS 
売上日
USING [fiscal];

//カレンダー対応
[calendar]: 
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
	Dual(Year(Addmonths($1,0)),YearStart($1,0,1))  AS [Year] Tagged ('$axis', '$year'),
    Dual(Month($1),Month(Addmonths($1,0))) AS [Month] Tagged ('$axis','$month','$cyclic'),
 	Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
    Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
	Dual(
		'W' & Num(Week($1,0),'00')
		,If(
			 Month($1)>=$(vFYStartMonth)
			,Week($1,0)-Week(YearStart($1,$(vFYStartMonth)),0)+1
			,Week($1,0)+Week(YearEnd($1,13+$(vFYStartMonth)),0)+1
		)
	) AS [Week] Tagged ('$axis','$weeknumber','$cyclic'),
	Date(WeekStart(Addmonths($1,0),0,0)) AS [WeekStart] Tagged ('$axis','$weekstart','$cyclic'),
	Year($1) & 'W' & Num(Week($1,0),'00') AS [YearWeek] Tagged ('$axis','$yearweek','$qualified'),
	Dual(Num(Month($1),'00') & '/' & Num(Day($1),'00'),
    	Month(Addmonths($1,0))*100+Day($1))AS [MonthDay] Tagged ('$axis','$monthday','$cyclic'),
	Date(Floor($1)) AS [Date] Tagged ('$axis', '$date','$qualified'),
    Day($1) + weekday(MonthStart($1)) As [CalendarPosition] Tagged ('$axis','$weekday','$cyclic');
DERIVE FIELDS FROM FIELDS 
売上日
USING [calendar];