Qlik SenseのValueList関数を利用したP/L作成方法

はじめに

Qlik Senseは非常に使いやすく個人的にもお気に入りのBIツールですが、ピボットテーブルの表現について一部不便さを感じていました。
ピボットテーブル上で 単純にメジャー、軸を指定するだけではピボットテーブルの縦方向、もしくは横方向で同一の計算式で表現できる集計表しか実装できない ためです。
そのため、従来エクセルで行っていた会社や部門のP/LをQlik Senseに切り替えようとした場合、そのままのレイアウトでは実現できず、複数のピボットテーブルに分ける必要があります。

ところが、Qlik Senseの ValueList関数 を利用することで自由度高くピボットテーブルを実装可能であることがわかりました。
同じようにQlik Senseのピボットテーブルの実装に限界を感じていた方の参考になればと思い、今回記事を作成しました。

ValueListを利用しない場合のピボットテーブルの制約

Qlik Senseのピボットテーブルではメジャーは行または列のいずれか片方に設定が可能です。
列にメジャーを設定した場合は、該当の列の全ての行に対して同一の計算式が適用されるため、例えば「売上」のような特定の科目の集計のみで算出できるものと、「利益(＝売上マイナス原価)」の様な計算で算出するものを同じ列上に混在させることはできません。

ValueListを利用した場合のピボットテーブルの表現

ValueListを利用することで、同じ列の中で行ごとに異なる計算式を設定することが可能となります。
そのため、1行目は「売上」を集計する計算式、2行目は「売上」から「原価」を差し引いた「利益」を算出する計算式、といった具合に異なる計算式を混在させることが可能となります。

サンプルコード

ValueListを利用して実装したサンプルコードを掲載します。
サンプルコードの実装にあたり、まずは利用するサンプルデータを登録します。

サンプルデータ

今回は以下の3つのデータを登録しました。
受注情報は販売管理システム、会計情報(売上、売上原価、営業費用)は会計システムのデータを利用する想定でファイル、レイアウトを分けています。
一般的には受注に対しても予算がありますが、今回は割愛しています。

#	データ内容	ファイル名
1	受注の実績データ	PL検証用サンプルデータ_受注実績.csv
2	会計の実績データ	PL検証用サンプルデータ_会計実績.csv
3	会計の予算データ	PL検証用サンプルデータ_会計予算.csv

受注の実績データ レイアウト

会計年度	伝票番号	伝票日付	金額	組織
2020	Order-001	2020/4/1	1,000	組織A
2020	Order-002	2020/4/1	800	組織A
2020	Order-003	2020/4/1	500	組織A
2020	Order-004	2020/4/2	600	組織A
…	…	…	…	…

受注伝票毎にレコードを持つ想定です。

会計の実績データ レイアウト

会計年度	伝票番号	伝票日付	科目	金額	組織
2020	Fin-0001	2020/4/1	売上	1,200	組織A
2020	Fin-0002	2020/4/1	直接原価	400	組織A
2020	Fin-0003	2020/4/1	間接原価	300	組織A
2020	Fin-0004	2020/4/2	営業費用	200	組織A
…	…	…	…	…	…

会計伝票(仕訳)毎にレコードを持つ想定です。

会計の予算データ レイアウト

会計年度	月	科目	金額	組織
2020	4	売上	1,500	組織A
2020	4	直接原価	400	組織A
2020	4	間接原価	200	組織A
2020	4	営業費用	300	組織A
2020	5	売上	1,200	組織A
…	…	…	…	…

月毎、科目毎にレコードを持つ想定です。

ロードスクリプトの登録

ロードスクリプトにデータの読み込み設定を行います。
今回は3つのデータをConcatenateして、1つのファクトテーブルにまとめた上で集計します。
「伝票番号」「伝票日付」「金額」について、今回の実装では名前を変える必要はありませんが、
受注と会計では意味合いが違うため別名でロードしています。

集計の年月を揃えるために、実績データは「伝票日付」、予算データは「会計年度」「月」を元に「年月」「月」カラムを作成しています。

ロードスクリプト記載例１（Concatenate）

[Concat_Fact]:
Load
	'Order' as データ種別,
    会計年度,
    伝票番号 as 販売伝票番号,
    伝票日付 as 受注日付,
    date(date#(伝票日付,'YYYY/M/D'),'YYYY') as 年,
    date(date#(伝票日付,'YYYY/M/D'),'YYYYMM') as 年月,
    date(date#(伝票日付,'YYYY/M/D'),'M') as 月,
    金額 as 受注額,
    組織
From [lib://DataFiles/PL検証用サンプルデータ_受注実績.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

Concatenate(Concat_Fact)
Load
	'FI_Act' as データ種別,
    会計年度,
    伝票番号 as 会計伝票番号,
    伝票日付,
    date(date#(伝票日付,'YYYY/M/D'),'YYYY') as 年,
    date(date#(伝票日付,'YYYY/M/D'),'YYYYMM') as 年月,
    date(date#(伝票日付,'YYYY/M/D'),'M') as 月,
    科目,
    金額,
    組織
From [lib://DataFiles/PL検証用サンプルデータ_会計実績.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

Concatenate(Concat_Fact)
Load
	'FI_Forcast' as データ種別,
    会計年度,
    if(月>=4,date(date#(会計年度&月,'YYYYM'), 'YYYYMM'),date(date#((会計年度+1)&月,'YYYYM'), 'YYYYMM')) as 年月,
    if(月>=4,date(date#(会計年度&月,'YYYYM'), 'M'),date(date#((会計年度+1)&月,'YYYYM'), 'M')) as 月,
    科目,
    金額,
    組織
From [lib://DataFiles/PL検証用サンプルデータ_会計予算.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

Concatenateの他、年度切り替え用の変数を定義しておきます。

ロードスクリプト記載例２（変数の定義）

Set vFY = '=[会計年度]';

マスターアイテムの登録

マスターアイテムを次の通りに登録します。
マスターアイテムを登録しておくことで、複数の設定箇所での使い回しや、設定変更時の影響箇所の極小化が可能となります。

#	名前	数式
1	当年実績_受注	Sum({$<会計年度={$(vFY)},データ種別={"Order"}>}[受注額])
2	当年実績_売上	Sum({$<会計年度={$(vFY)},データ種別={"FI_Act"},科目={"売上"}>}[金額])
3	当年実績_売上原価	Sum({$<会計年度={$(vFY)},データ種別={"FI_Act"},科目={"直接原価"}>}[金額]) +Sum({$<会計年度={$(vFY)},データ種別={"FI_Act"},科目={"間接原価"}>}[金額])
4	当年実績_営業費用	Sum({$<会計年度={$(vFY)},データ種別={"FI_Act"},科目={"営業費用"}>}[金額])
5	当年予算_受注	0
6	当年予算_売上	Sum({$<会計年度={$(vFY)},データ種別={"FI_Forcast"},科目={"売上"}>}[金額])
7	当年予算_売上原価	Sum({$<会計年度={$(vFY)},データ種別={"FI_Forcast"},科目={"直接原価"}>}[金額]) +Sum({$<会計年度={$(vFY)},データ種別={"FI_Forcast"},科目={"間接原価"}>}[金額])
8	当年予算_営業費用	Sum({$<会計年度={$(vFY)},データ種別={"FI_Forcast"},科目={"営業費用"}>}[金額])

ビボット表の作成

チャート一覧から「ピボットテーブル」を選択し表を作成します。

軸の設定

軸の設定

=ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益')

値の設定

IF文でValueListに記載した値ごとに計算式を記載します。

値の設定（当年予算）

=IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '受注', 当年予算_受注,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上', 当年予算_売上,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上原価', 当年予算_売上原価,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上総利益', 当年予算_売上 - 当年予算_売上原価,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '営業費用', 当年予算_営業費用,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '営業利益', 当年予算_売上 - 当年予算_売上原価 - 当年予算_営業費用,
))))))

値の設定（当年実績）

=IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '受注', 当年実績_受注,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上', 当年実績_売上,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上原価', 当年実績_売上原価,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上総利益', 当年実績_売上 - 当年実績_売上原価,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '営業費用', 当年実績_営業費用,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '営業利益', 当年実績_売上 - 当年実績_売上原価 - 当年実績_営業費用,
))))))

値の設定（予実差）

=IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '受注', 当年実績_受注 - 当年予算_受注,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上', 当年実績_売上 - 当年予算_売上,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上原価', 当年実績_売上原価 - 当年予算_売上原価,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上総利益', (当年実績_売上 - 当年実績_売上原価) - (当年予算_売上 - 当年予算_売上原価),
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '営業費用', 当年実績_営業費用 - 当年予算_営業費用,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '営業利益', (当年実績_売上 - 当年実績_売上原価 - 当年実績_営業費用) - (当年予算_売上 - 当年予算_売上原価 - 当年予算_営業費用),
))))))

値の設定（予実比）

=IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '受注', 当年実績_受注 / 当年予算_受注,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上', 当年実績_売上 / 当年予算_売上,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上原価', 当年実績_売上原価 / 当年予算_売上原価,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '売上総利益', (当年実績_売上 - 当年実績_売上原価) / (当年予算_売上 - 当年予算_売上原価),
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '営業費用', 当年実績_営業費用 / 当年予算_営業費用,
 IF( ValueList('受注', '売上', '売上原価', '売上総利益', '営業費用', '営業利益') = '営業利益', (当年実績_売上 - 当年実績_売上原価 - 当年実績_営業費用) / (当年予算_売上 - 当年予算_売上原価 - 当年予算_営業費用),
))))))

サンプルチャート画面

作成したサンプルチャートの画面です。
サンプルチャートの様に、行の軸には設定したValueListの他、通常の軸(組織)も指定し複数階層の軸とすることが可能です。

おわりに

ValueList利用することで、項目ごとに計算式が異なる複雑なクロス集計表を実装することが可能になります。
また、今回はピボットテーブルに対してValueListを利用しましたが、棒チャート等の他のチャートとValueListを組み合わせて利用することも可能です。
各チャートにValueListを組み合わせることで表現可能な内容については別の記事でまとめたいと思います。

最後まで読んでいただきありがとうございました。