はじめに
Qlik Senseで期間ごとに集計したい！
元データが大きいのでデータロードで集計をすませておきたい！
そんな時に使える集計方法をご紹介いたします。
月ごとの集計
月ごとの集計値を出すために「年月カレンダー」テーブルと「年月」カラムを用意し、年月ごとに集計を行っていきます。
事前準備：
元データとして、日付（[Date]）と販売数（[Quantity]）を持つテーブルを用意しました。
このデータを下記の手順で処理していきます。
- 販売数テーブルに「販売年月」を持たせる
- 「年月カレンダー」を作成する
- 「年月カレンダー」の年月を用いて、「販売年月」ごとに販売数を集計する
１．販売数テーブルに「販売年月」を持たせる
はじめに、元データを「販売数」テーブルとしてロードする際、「販売年月」カラムを追加します。
販売数:
LOAD
Date(Date#([Date], 'YYYY/MM/DD')) AS 販売日,
Date(MonthStart(Date#([Date], 'YYYY/MM/DD')), 'YYYY/MM') as 販売年月,
[Quantity] as 販売数
FROM [lib://DataFiles/TesData.csv]
(txt, utf8, embedded labels, delimiter is ',', msq)
where Len(Trim(Date))<>0;
LOAD
Date(Date#([Date], 'YYYY/MM/DD')) AS 販売日,
Date(MonthStart(Date#([Date], 'YYYY/MM/DD')), 'YYYY/MM') as 販売年月,
[Quantity] as 販売数
FROM [lib://DataFiles/TesData.csv]
(txt, utf8, embedded labels, delimiter is ',', msq)
where Len(Trim(Date))<>0;
このようなデータになります。
２．「年月カレンダー」を作成する
次に、年月ごとのカレンダーを作成します。
販売数テーブルの最小販売日～最大販売日の期間のみ作成されるようにしたいと思います。
最小販売日と最大販売日を取得して年月に直し、変数に格納します。
//最小販売日を取得
Min販売日:
Load
Min(Floor(販売日)) as Min販売日
Resident 販売数;
//最大販売日を取得
Max販売日:
Load
Max(Floor(販売日)) as Max販売日
Resident 販売数;
//最小販売日と当日日付(最大販売日)を変数に格納
Let vMinDate = FieldValue('Min販売日', 1);
Let vMaxDate = FieldValue('Max販売日', 1);
作成した「最小販売日」と「最大販売日」を用いてカレンダーを作成します。
この後のループ処理の都合上、日付を月初で固定した「年月月初」を持たせています。
//最小販売日～最大販売日間のカレンダーを作成
年月カレンダー:
Load
AddMonths(MonthStart(Date($(vMinDate),'YYYY/MM/DD')),IterNo()-1) as [年月初日]
AutoGenerate 1
While AddMonths(MonthStart(Date($(vMinDate),'YYYY/MM/DD')),IterNo()-1) <= MonthStart(Date($(vMaxDate),'YYYY/MM/DD'));
年月カレンダーができました。
３．「販売年月」ごとに販売数を集計する
最後に、「年月カレンダー」の「年月初日」を用いて、「販売年月」ごとに販売数を集計していきます。
//年月ごとに集計処理
Let vMaxIter = NoOfRows('年月カレンダー');
For i=1 to $(vMaxIter)
Let vDate = FieldValue('年月初日', $(i));
月別販売数:
Load
販売年月,
Sum(販売数) as 月別販売数
Resident 販売数
Where 販売年月 = '$(vDate)'
Group By 販売年月;
Next
Drop Table Min販売日, Max販売日, 年月カレンダー;
販売数が年月ごとに集計されました！
おわりに
今回は年月での集計をご紹介しましたが、集計範囲次第で週や年での集計も可能です。
Qlik Senseはデータロード機能を持つため、データ量が多くシートの負荷を削減したい場合にデータロードでの加工に切り替えられるなど、選択肢が広くなっております。
当記事がみなさまの参考になりましたら幸いです。
