Preppin Data Practice #07 （24年10月 2024: Week 39 - Preppin' Consultancy Ranks
Tableau Prepユーザー会のNakajima2です。
Japan Preppin Data Fam 第7回目のPreppin Data勉強会、24年10月のYouTube動画公開は、24年9月にPreppin' Dataで出題された全４題（W 36 〜39）から2024W39 の課題にチャレンジです。
今回取り上げたWeek 39のチャレンジでは、前週のWeek38で作成したデータフローからの出力結果を元に処理を進めることになっています．コンサルティングの契約内容をクリーンアップし、重複する契約がないことを確認しました。
今回の課題では、その契約に関する分析を行います。各グレードや組織全体で、最も稼いでいるコンサルタントが誰なのかを理解することが目的となっています．
データソースの内容は、実務でもよくありそうなデータが整っていないデータを処理を行うケースになります。
コンサルティングの契約日数については、契約開始日から契約終了日の差で求めます。
今回のデータでは、契約開始日よりも契約終了日が日程的に前になっているケースが多く含まれています。これは、契約期間の契約日数を計算するとマイナスの値になっている状況です。
このマイナスの契約日数が含まれた状態の元データのため、勉強会参加者は大いに悩みました。
・契約日数がマイナスになることは正しくないとして、本来あるべき計算をすべきか？
・契約日数がマイナスになることを承知して、単純に日数差を計算して良しとしてしまうか？
・契約日数がマイナスの時の給与計算はどうするのか？
課題で提示されたアウトプットのデータからは、契約日数はマイナスで表示されており、そのマイナス期間での給与は新しい行の仕様からか1日、もしくは2日分の給与として計算させる結果となっています。
この悩み大き課題に、勉強会のメンバーは、どのようなプレップ処理で対応するか？
スッキリしないモヤモヤした感覚を持ちつつ、メンバーがそれぞれにTipsを持ち寄り対処した結果をご覧ください。
Preppin Data勉強会の配信動画（YouTube）はこちらです
１）課題の内容
今回取り上げたW39のPreppin Data 課題は、下記を参照ください。
https://preppindata.blogspot.com/2024/09/2024-week-39-preppin-consultancy-ranks.html
・出題の背景、対応項目
今回の課題は、前週に扱ったデータの続きで処理を行うケースになります。この課題は、コンサルティング会社の契約内容の整理に関する対応になります。前の週の課題では、与えられたデータソースからコンサルティングの契約内容をクリーンアップし、重複する契約がないことを確認しました。
今回の課題では、その契約に関する更なる分析を行います。各グレードや組織全体で、最も稼いでいるコンサルタントが誰なのかを理解することが目的になります。
・データソース、Outputデータ
データソース
Excelファイルで提供されたワークシートになります。
アウトプット
7フィールド、718列になる次のようなデータです。
7 data fields:
Calendar Days
Initials
Engagement Order
Grade Name
Day Rate
Overall Rank
Grade Rank
718 rows (719 incl. headers)
２）対応のポイント（Requirementsのポイント）
要求項目から、処理として対応すべき概要は次の通りです（要約した内容です）。
1. データを入力する
データをインポートします。
2. コンサルタントが契約中の各日を1行に作成する
各コンサルタントの契約期間中の各日を1行として扱います。
3. 週末を除外する
週末（土曜・日曜）の日を除外します。
4. 契約期間中のカレンダー日数を算出する（週末を含む）
週末を含む契約期間中のカレンダー日数を計算します。
5. データを集計する
以下のデータを集計します：
• コンサルタントが契約中に従事したカレンダー日数
• 各個人が契約ごとに稼いだ合計金額
• 契約番号、イニシャル、グレードを保持する
6. 契約ごとにコンサルタントを日給ベースでランク付けする
日給に基づいて、以下のようにコンサルタントをランク付けします：
• 全体のランク
• グレードごとのランク
出力
集計したデータを出力する。
この課題に基づいてデータを処理し、コンサルタントのパフォーマンスを評価します。
３）参加者の解答例、Tipsなど
（A) 日付データの調整（契約日数の算出） ： 新しい行の利用
たっくんさんは、課題の要求通りの処理を実施しています。
契約開始日（データフィールド名 [Engagement Start Date] ）と契約終了日（データフィールド名[Engagement End Date] ）を利用し、新しい行の処理から全データに必要な日付データを作成しています。
単純に契約日を算出する際は、契約開始日と契約終了日の差を取る計算を用います。
[Calendar Days]
DATEDIFF('day',[Engagement Start Date],[Engagement End Date])
（B) 週末日数の削除
たっくんさんは、合わせて週末日数の削除を実施するために、Datename関数を用いて上記で作成した日付データに曜日を示すフィールドを作成しています。その後、デフォルトのフィルタ機能で’Saturday'と ’Sunday’のデータとして週末を削除しています。
[Weekday]
DATENAME("weekday",[Engagement Day])
一方、Yamaguchiさんは、たっくんさん同様に曜日の作成を行なっていますが、週末の削除も含めて一つのフィルタ処理で作業を完結させています。
日付データから週末（土日）のレコードだけを無くすと考え処理を進めています。
DATENAME("weekday",[Engagement Day]) <> "Saturday"
AND
DATENAME("weekday",[Engagement Day]) <> "Sunday"
Nakajima2は、日付の曜日をDatepart関数を用い数値として求め、週末を削除するフィルターをかけました。
[Engagement Days Weekday]
DATEPART('weekday',[Engagement Days])
[Engagement Days Weekday] >1 AND [Engagement Days Weekday] < 7
（C) 契約日数がマイナスになる点を考慮した前処理
mitamuuさんは、契約日数が通常のプラスになる場合と、マイナスになる場合を仕分けして別の計算として分けてフローを計算する方法をとっています。
データを契約開始日と契約終了日の大小関係を比較する式でフィルターをかけてデータを仕分けします。
通常の契約終了日が大きくなる場合のフィルター式
NOT ([Engagement Start Date] <= [Engagement End Date])
逆に、契約終了日が小さくなる場合のフィルター式
[Engagement Start Date] <= [Engagement End Date]
その後、各コンサルタントの契約日数から週数を計算します。
この結果を元に、週末を除いた日数（フィールド名 [Remaining Days]）と、契約日数（フィールド名 [Number of Days]）を算出しています。
＊それぞれのフィールドは、最終的に[Day Rate] と[Calendar Days] に変更
されます
週末を除く計算は、契約開始日の曜日を元にしてそれぞれに必要となる日数を計算しています。契約日数は、上記までの各フィルードより計算式て求めています。
IF [Remaining Days] = 0 THEN 0
ELSE
CASE DATEPART('weekday',[Engagement Start Date])
WHEN 1 THEN // 日曜日
[Remaining Days] - 1
WHEN 2 THEN // 月曜日
IF [Remaining Days] >= 6 THEN [Remaining Days] - 1
ELSE [Remaining Days]
END
WHEN 3 THEN // 火曜日
IF [Remaining Days] >= 6 THEN [Remaining Days] - 2
ELSEIF [Remaining Days] = 5 THEN [Remaining Days] - 1
ELSE [Remaining Days]
END
WHEN 4 THEN // 水曜日
IF [Remaining Days] >= 5 THEN [Remaining Days] - 2
ELSEIF [Remaining Days] = 4 THEN [Remaining Days] - 1
ELSE [Remaining Days]
END
WHEN 5 THEN // 木曜日
IF [Remaining Days] >= 4 THEN [Remaining Days] - 2
ELSEIF [Remaining Days] = 3 THEN [Remaining Days] - 1
ELSE [Remaining Days]
END
WHEN 6 THEN // 金曜日
IF [Remaining Days] >= 3 THEN [Remaining Days] - 2
ELSEIF [Remaining Days] = 2 THEN [Remaining Days] - 1
ELSE [Remaining Days]
END
WHEN 7 THEN // 土曜日
IF [Remaining Days] >= 2 THEN [Remaining Days] - 2
ELSEIF [Remaining Days] = 1 THEN [Remaining Days] - 1
ELSE 0 // 土曜日のみの場合は0にする
END
END
END
手間がかかっている計算式ですが、新しい行と後述の集計処理を利用せずユニオンでデータを集約できることから、処置中の創業数を大きく増やすことなく処理ができ、処理自身はコンパクトに処理速度を早めら得れる可能性がある仕方となっています。
（D)給与データの計算
[Day Rate] と[Calendar Days] のフィールドが揃った後は、集計処理で各データを計算出来ます。
[Calendar Days] は集計時にグループ化して利用し、[Day Rate] 集計フィールド側でSUMで集計を行います。
（E) Rank計算
2種のランク計算は、PartitionとOrderbyを使い分けて実施します。
以下は、Yamaguchiさんの処理方法です。
[Grade Rank]
{ PARTITION [Grade Name] : { ORDERBY [Day Rate] DESC : RANK() }}
[Overall Rank]
{ ORDERBY [Day Rate] DESC : RANK()}
４） 契約日数がマイナスになっている場合の新しい行の処理内容
契約日数を算出した場合（Datadiff関数を用いた計算）で、通常のプラスになる場合と、マイナスになる場合が出てきます。
プラスの場合は、新しい行で契約開始日と契約終了日の間で日付が追加されますが、マイナスになる場合は下図の様に契約開始日と契約終了日の２行しか作成されない状況でした。
今回の課題では、この新しい行の仕様としての処理から算出される数値がOutputのデータに合う形になります。
契約日数がマイナスになる場合は、開始日の曜日（土曜日か日曜日か）によって計算され方が異なっているようです。
mitamuuさんは、この点を補正するために条件式の計算時に 1 と 2 で補正する処理をしたようです。
データの処理においては、契約開始日と契約終了日の大小関係をしっかり確認して利用すべきとのメンバー間の議論結論になりました。
InputデータとOutputデータの確認は、ちゃんとするようにしましょう。
5）参加者が回答したPrep フローファイル
勉強会に参加したメンバーが作成したPrepフローのファイルを公開致します。
このブログ、動画アーカイブをご覧頂いたみなさまで、ご自分で手を動かしフロー作成をされた方の少しでもご参考になればと思っています。
下記のリンク先にフローファイルを保存しています。みなさまのお役に立てれば幸いです。
https://drive.google.com/drive/folders/18IUvtvhhOV1uCCqsUXojXJg1OlP_m8So?usp=share_link
6）おわりに
今回で7回目の勉強会 公開配信（ビデオ解説）になります。
毎回感じることですが、他のメンバーから聞く発表で新たな発見があり、知識の習得、定着が深く図れていると感じています。
11月からPreppin' Data勉強会に初心者コースを開催することにしました。
・Prep使ってみたい
・仕事で使っているけど ちょっととっつきにくい
・Preppn' Dataの初心者向け課題を始めました
とお思いの方、一歩踏み出してスタートすると想像以上に力がつきますよ。
Tableau Prepが使い慣れた中級以上の方も、Tipsが 目から鱗いっぱいありますので、是非ご参加ください。
参加希望の方は、下記までメールご連絡をお願いします。
Tableau Prepユーザー会 ： tableauprep.usergroup@gmail.com