2024: Week 39 - Solution


Solution by Tom Prowse and you can download the workflow here


Step 1 - Each Day of Engagement

First, we want to ensure that there is a row for each day that a consultant is on an engagement. For this we can use the New Rows step within Prep to help pad out any missing days. Within the setup we want to add new rows between the engagement start and end dates and have an increment of 1 day: 



We can then remove the weekends from the list by first identifying the weekday by using the Datename function: 

Weekday
DATENAME('weekday',[Work Day])

Then from this field we can exclude the weekends (Saturday & Sunday). 

Finally we can calculate the number of calendar days for each engagement: 

Calendar Days
DATEDIFF('day',[Engagement Start Date],[Engagement End Date])

At this stage the table should look like this: 



Step 2 - Aggregate & Rank 

The final part of the challenge is to aggregate our table as per the requirements. For this we can use the aggregate step where we group by Initials, Engagement Order, Grade Name and then calculate the average Calendar Days, and sum the Day Rate: 



After we have aggregated we can rank the consultants by their Overall Rank and their Grade Rank: 

Grade Rank




Overall Rank



Once we have calculated both ranks we are ready to output our table: 


You can view the output here.

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja@JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help! 

Popular posts from this blog

2024: Week 1 - Prep Air's Flow Card

2023: Week 1 The Data Source Bank

How to...Handle Free Text