複数項目の累積について

[UTMKIT_56]

お世話になっております。
複数項目の累積についてお伺いさせて下さい。

日付、分類1、分類2、個数といったテーブルで、
分類2が3か月だったら過去3か月分、
6カ月だったら過去6カ月分、
1年だったら過去1年分の累積を算出し、
個数が0の日付の場合でも累積は表示させたいと考えています。

軸を日付、分類1、分類2としたの場合、
個数が0の日付が無視され、個数が入っている日付分で累積表示されてしまいます。
そのため過去3か月分を取りたい場合でも誤った数字になってしまいます。
また、個数がない日付には累積の表示ができておりません。
※above(sum({$<日付>}個数))で日付を無視して可能かと思ったのですができておりません。

日付で累積を算出し、
また、個数0だったとしても累積を表示させる方法はありますでしょうか。

お手数をおかけいたしますが、ご教示お願い致します。

[hka · Accepted solution]

データがない部分を表現するには実データとはリンクしていない日付の軸用テーブルを作成する方法があります。

添付ファイルを見てもらうとわかりますが、日付1という項目を持つデータテーブルを作成し

日付1を軸にしたピボットテーブルを作成し、match関数で日付と日付１が一致した場合に個数を合計し

それ以外は0とする数式

  sum(if(match(日付,日付1),個数,0))

を作成することで、データのない部分に0を挿入します。

あとはこの数式を使用して累積計算を行います。

この方法はデータ量が多くなるとパフォーマンスが落ちることもありますので、

事前にスクリプトでデータのない部分に0を挿入するデータを作成した方が運用面では現実的かもしれません。

[UTMKIT_56]

Hka様

まさしくこれがやりたいことでした！

実データと関係のない軸用のテーブルを作成するやり方があるんですね。勉強になりました。

ただ、実ファイルはデータ量が多いのでスクリプト側での対応を検討したいと思います。

ありがとうございました。

[ShoNakajima]

スクリプトでも書いてみました。日付とその繰り返しの取り扱いに注意が必要ですが、コード量としては十数行程度でできました。ご参考まで。

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

TEMP:
Load Date(Date#(日付&01,'YYYYMMDD'),'YYYYMM') as 日付,分類1,分類2,個数;
//日付カラムをDate型で扱うためにDate#,Date関数で変換。見かけ上はYYYYMM表示となるが、裏では日付として各月の第一日(例：20181001)として保持している。
//のちの繰り返し処理をシンプルにするため。
LOAD * INLINE [
日付,分類1,分類2,個数
201810,2,6か月,76
201810,2,3か月,74
201811,1,3か月,11
201811,1,6か月,46
201812,1,6か月,76
201812,1,6か月,68
201812,1,1年,15
201812,1,1年,51
201901,1,1年,60
201901,1,3か月,11
201902,1,6か月,58
201903,1,1年,24
201903,2,1年,21
201903,1,3か月,61
201903,2,6か月,82
201904,1,3か月,54
201904,2,3か月,82
201904,2,6か月,48
201906,2,3か月,37
201906,1,3か月,31
201906,2,6か月,79
201906,2,1年,65
201908,2,3か月,25
201908,2,3か月,2
201908,2,3か月,53
201909,2,6か月,53
201909,1,3か月,33
201909,2,3か月,29
201910,1,3か月,86
201910,1,1年,70
];
//後述の計算のために、各レコードにトランザクションデータの最初と最後の日付をJoin
Join
LOAD
	 Min(日付) as StartDate,
	 Max(日付) as EndDate
Resident TEMP
;

//StartDateとEndDateの間のすべての月初日付をWhileループで作成
FinalTable:
LOAD 
	 AddMonths(StartDate, IterNo() - 1) as [日付],
	 分類1,
     分類2,
     If([日付] =  AddMonths(StartDate,IterNo() - 1), 個数, 0) as 個数
     //この[日付]はTEMPテーブルの日付項目。このテーブルで作成した日付（AddMonths～）と一致すれば個数の値を入れ、そうでなけば0を入れている
resident TEMP
While  AddMonths(StartDate,IterNo() - 1) <= EndDate;

//最後に、TEMPテーブルを落とす。
Drop Table TEMP;