2024: Week 23 - Solution

Solution by Tom Prowse and you can download the workflow here. 

Step 1 - Combine Salaries & Tenures

In order to combine the Salaries and Tenures tables, we need to ensure that the Names in the Salaries table are in Title case. We can use the Proper() function to do this and make sure that they match the Tenures table. 

Name 

PROPER([Name])

We can now join these tables together using the Name field: 

Step 2 - Tenure 

Now we have both tables together we can make sure the End Dates are formatted correctly by changing the field to a date and making sure that the '28/05/2024' is changing to '05/06/2024'.

We can then use this end date to calculate the tenure: 

Tenure

DATEDIFF('day',[Start Date],[End Date])

Then use the Tenure to calculate the Expected Total Salary: 

Expected Total Salary 

ROUND(([Tenure]/365)*[Salary])

After these calculations the table should look like this: 

Step 3 - Amount Paid

Finally we can combine the Amount Paid table by using an inner join on the Name field: 

We can then filter the table so that we are only showing the people who haven't been paid the correct amount by using the calculation: [Expected Total Salary]!=[Amount Paid]

Finally we want to keep only the required fields and the output should look like this: 

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!