QuickSight初心者です。
社員の名前と入社日、退社日のカラムを持つテーブルから在籍人数の推移を計算する方法を教えて頂けませんか？
@y_shoji
ご質問いただきありがとうございます。
名前、入社日、退社日の3つのカラムを持つテーブルがあるのはわかりました。
そこで、在籍人数の推移をどのように求めたいのか、具体的にお聞かせいただけますでしょうか？
おそらくですが、集計方法は、以下のいずれかになるのではないかと思います。
- 日単位の在籍人数
- 月単位の在籍人数
- 四半期単位の在籍人数
- 年単位の在籍人数
それぞれによって、集計方法が変わってきますので、お聞かせいただけないでしょうか？
「2. 月単位の在籍人数」をグラフで可視化したいと考えております
具体的には、「クラスター棒コンボグラフ」にて棒グラフに月単位の入社した人数、退職した人数
折れ線グラフに月単位の在籍人数の推移を表示したいと考えております。
下記の点、質問の際に記入を間違えておりました。
(誤)退社 (正)退職
@y_shoji
特定の時点で何人所属していたかを把握するには、
日付マスタのようなデータと結合して任意の時点で在籍していたユーザー一覧のような
データを生成する必要があるかと思います。
データを作成するにはSQLを書く必要があるのですが、
ここでは、Amazon Redshiftでデータが管理されており、
QuickSightからSQLを書いてデータを作成する前提でご説明します。
テーブル1: employment_history
2024年1月から表示するとして、以下のようになるかと思います。
- 2024年1月時点で、在籍:18名 + 入社:2名 = 計20名
- 2024年2月時点で、在籍:20名 + 入社:3名 - 退職:1名(1月退職者) = 計22名
- 2024年3月時点で、在籍:22名 + 入社:3名 - 退職:2名(2月退職者) = 計23名
テーブル2: calendar
1日単位の連続した日付データです。
QuickSight上で、Amazon Redshiftのデータソースから、
以下のようなSQLを書いてデータセットを作成(※)します。
※データセットを作成する際に、カスタム SQL を使用する をクリックすることで、
SQLの結果からデータセット作成が行えます。
例
WITH cal AS (
SELECT
*
FROM
calendar
WHERE
"カレンダー日付" BETWEEN date('2024-01-01') AND date('2024-03-31')
)
,history as (
SELECT
"氏名"
,"入社日"
,CASE WHEN "退職日" IS NULL THEN date('2999-01-31') ELSE "退職日" END AS "退職日"
FROM
employment_history
)
SELECT
*
FROM
cal
LEFT JOIN history
ON history."入社日" <= cal."カレンダー日付" AND cal."カレンダー日付" <= history."退職日"
QuickSightでデータセットの作成が完了したら、分析にて以下の計算フィールドを作成します。
従業員数
distinct_count(氏名)
入社者数
distinct_countIf(氏名,truncDate('MM',カレンダー日付)=truncDate('MM',入社日))
退職者数
distinct_countIf(氏名,truncDate('MM',カレンダー日付)=truncDate('MM',退職日))
クラスター棒コンボグラフを選択し、以下のように設定します。
以上、ご参考になれば幸いでございます。
2 Likes
回答ありがとうございました。
参考に致します。