Qlik : 期間累計・直近●ヶ月

最近コロナで「直近1週間の平均感染者数」とかいう単語をよく見るようになりました。

この「直近●●の合計」など、期間累計のやり方についてです。

チャートを使えば、RangeSum/Aboveで期間累計できますが、ロードスクリプトだとそのやり方を知りません。

なので、データハンドリングで対処してます。

私は2つのやり方でやっているのですが、1つはすべての組み合わせを作ってやる方法(デカルト積)、もう一つは「過去からの総合計から●ヶ月前までの総合計を引く」方法です。

先に、テストデータを載せておきます。

//テストデータ
testdata:
	load		*
	Inline [
		prod, ym, amt
		魚, 201901, 10
		魚, 201902, 20
		魚, 201903, 30
		魚, 201904, 40
		魚, 201905, 50
		魚, 201906, 60
		魚, 201907, 70
		魚, 201908, 80
		魚, 201909, 90
		魚, 201910, 100
		魚, 201911, 110
		魚, 201912, 120
		魚, 202001, 10
		魚, 202002, 20
		魚, 202003, 30
		魚, 202004, 40
		魚, 202005, 50
		魚, 202006, 60
		魚, 202007, 70
		魚, 202008, 80
		魚, 202009, 90
		魚, 202010, 100
		魚, 202011, 110
		魚, 202012, 120	
		肉, 201901, 10
		肉, 201902, 20
		肉, 201903, 30
		肉, 201904, 40
		肉, 201905, 50
		肉, 201906, 60
		肉, 201907, 70
		肉, 201908, 80
		肉, 201909, 90
		肉, 201910, 100
		肉, 201911, 110
		肉, 201912, 120
		肉, 202001, 10
		肉, 202002, 20
		肉, 202003, 30
		肉, 202004, 40
		肉, 202005, 50
		肉, 202006, 60
		肉, 202007, 70
		肉, 202008, 80
		肉, 202009, 90
		肉, 202010, 100
		肉, 202011, 110
		肉, 202012, 120	
	]
;

すべての組み合わせを作ってやる(デカルト積)

元のデータに元のデータをどばっと総当たりでくっつけて、期間に絞って集計するやり方です。と、文章では説明が難しいので、以下のサンプルプログラムをご覧ください。過去1年間の期間累計のサンプルです。

2019/01のレコードに2019/01~2020/12のレコードをくっつける、
2019/02のレコードに2019/01~2020/12のレコードをくっつける、
・・・
2020/12のレコードに2019/01~2020/12のレコードをくっつける、
と一時的にデータが膨らみます。
その後、2020/12のレコードは「(ym2が)2020/1~2020/12までのamtをsumして、元のデータにくっつける」という処理の流れになります。

なお、このやり方だと、一時的にデータが膨大に膨れます。なので、巨大データを扱う際は不向きです。

//--- 期間累計のやり方1:デカルト積(総当たり)
temp: NoConcatenate
	load		prod, ym
	Resident	testdata
;
Outer Join
	load		prod, ym as ym2
			  , amt
	Resident	testdata
;

Left Join(testdata)
	load		prod
			  , ym 
			  , sum(amt)		as amt_直近1年
	Resident	temp
	Where		ym2 <= ym
			and ym2 >  ym-100
	Group By	prod
			  , ym
;

drop tables temp ;

過去からの総合計から●ヶ月前までの総合計を引く

こちらのほうが直感的かもしれません。

以下のサンプルプログラムでは、まず期間に関係ない(総)累計を計算し、その後1年前の累計をくっつけ、それを引いています。

データの始めから1年間経っていない部分(201901~201912)は、欠損になっています。

//--- 期間累計のやり方2:直近までの累計から1年前の累計を引く
temp:
	load		prod, ym
			  , if(Previous(prod) <> prod 
			  	  , amt
			  	  , peek(累計) + amt
			  	)				as 累計
	Resident	testdata
	Order By	prod, ym
;

//1年前の累計
left join
	load		prod 
			  , ym + 100 		as ym
			  , 累計				as 累計_1年前
			  
	Resident	temp
;

//引く
left join
	load		prod, ym
			  , 累計 - 累計_1年前	as amt_直近1年_2
	Resident	temp
;

//大元データにつけ戻し
left join(testdata)
	load		prod, ym
			  , amt_直近1年_2
	Resident	temp
;
drop tables temp ;

以上、ロードスクリプトでの累計のやり方でした。

カテゴリーQlik

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA