2021: Week 49 - Solution

 

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

The next instalment of our Departmental December challenges is all focused on Human Resources! We look at data related to our sales team and how long they have been with the company and how much that has cost us.

Step 1 - Employment Range

After we have input our table of data from the input file, we want to create an employment range field that captures how long the employee has been with the company. Within the requirements we need this in a MMM yyyy format for both the dates.

This process involves us finding the Min and Max dates for each employee and to make the formatting of dates a little easier, we are going to update the locale within the input step. We want it to be English (United Kingdom) as this will give us the correct date formatting for our requirements. 

From here we can then calculate the Min and Max employment dates for each of the employees. We want to maintain the row-level detail, therefore we can use Fixed LODs to calculate this: 

First Month

Last Month

Next we want to duplicate each of the newly calculated fields, so that we can convert them to the Month Name and Year number. We duplicate the fields so that we can use the in-built Tableau Prep functionality to convert these. 

Once we have converted the Months and Years for each the First and Last date then our data should look like this:

We now have each of the individual parts therefore we can bring these together to make the desired string using the following calculation: 

Employment Range 

LEFT([First Month-1],3)+' '+STR([First Month])+' to '+LEFT([Last Month-1],3)+' '+STR([Last Month])

The last step is to create the Reporting Year field, so we can duplicate the date and again use the convert date functionality to convert the date to a year, which we'll call Reporting Year. 

Then we can remove the unneeded fields so that we are left with the following table:

Step 2 - Yearly Totals

Next we want to calculate some yearly totals per employee. There are three areas that we want some information for including:

• Number of months they worked
• Their salary they will have received 
• Their sales total for the year

To calculate these we are going to use an aggregate tool to group by Name, Employment Range, and Reporting Year. Then aggregate the Sum of Number of Rows, Sum of Sales, and Avg of Annual Salary. 

From these newly aggregated fields, the Number of Rows becomes the Number of Months worked, Sales becomes Yearly Sales, and we can calculate the Annual Salary using the following calculation based on the number of month worked:

Annual Salary

([Annual Salary]/12)*[Number of Months]

Our data now looks like this:

Step 3 - Tenure

Within this step we are going to calculate a running total of the months that an employee has worked for us. To do this we are going to use a self-join technique where first we need to create a new step and keep only the Name and Reporting Year fields. These are going to be the fields that are important within the join. 

Using the new step, we want to join this back to our original data and join on Name = Name and Reporting Year >= Reporting Year. 

We use the Reporting Year join to help accumulate all of the additional months so that we can then use this to calculate the running total for tenure.

From here we can use an aggregation tool to calculate the Tenure field, where we group by Name and Reporting Year, then Sum the Number of Months: 

Within a new clean step we can rename the Number of Months to Tenure by End of Reporting Year, and the table should look like this: 

Notice how we have now lost all of the further details about each employee, therefore we need to use another join to bring back this information.

If we go back to the step before we calculated the tenure, we can create a new step where we want to remove the Number of Months field. After this step we can then join back to our existing workflow using the Name and Reporting Year fields. This will bring back the additional details about each of the employees whilst also maintaining the Tenure field. 

The data looks like this: 

Step 4 - Bonus Payments

The final step this week is to calculate how much bonus each of the employees has achieved for the year. This is based off of 5% of their salary and can be calculated by:

Yearly Bonus

[Yearly Sales]*0.05

Then the final steps are to tidy up a couple of the Salary fields. We want to do the following: 

• Rename Annual Salary to Salary Paid
• Round Salary Paid to 2dp - ROUND([Salary Paid],2) 
• Calculate the total paid using - ROUND([Salary Paid]+[Yearly Bonus],0)

That is the finally steps needed for the challenge this week so the output should look like this: 

You can download the full 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!