2022: Week 7 - Solution

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

This week's challenge comes from  Tableau Social Ambassador Kate Brown. 

In the challenge with want to combine various tables to help us monitor our metrics within the call centre. This is trying to replicate the scenario where you connect to a database table that has ids and another table with descriptor for those ids.

Step 1 - Location, People, & Leaders

The first step is to input the tables from the PeopleData Excel file. At first we want to bring in the Location, People, and Leaders tables and then combine these into a single table.

First we can join the Location & People by using the Location ID field:

Then we can join the Leaders table onto this by using Leader 1 = id: 

The final step here is to clean the Leader and Agent name fields so they are in the correct format. We can do this by using string calculations: 

Leader Name 

[last_name-1] + ', ' + [first_name-1]

Agent Name

[last_name] + ', ' + [first_name]

From here we can remove any of the fields that we no longer require so that our table looks like this: 

Step 2 - Add 2021 Dates

Next we want to add in the Date Dim table, so that we can start to combine the date fields as well. We 

Within the requirements we want to limit the dates to just 2021, so therefore we can use a filter to remove some results. Within the 'Filter Values' we can use the following calculation: 

YEAR([Month Start Date]) = 2021

This will keep only the dates from 2021. 

We can now combine the two branches, but the problem we have is there isn't a common field to do this join on. Therefore, we want to join all of the dates onto all of the 'people' rows by using a cross-join. To create the cross-join we need to create a field that is common across both tables. It doesn't matter too much what this is, so we have created a 'Join' calculation that is equal to 1.

Notice how the number of rows increases massively, as we are joining every row onto to each other.

After this join our table should now look like this: 

Step 3 - Join Metrics

For the next part we want to include the metrics table into our workflow. Before joining though we need to do some tidying on the Metrics table. 

The Metrics input is split into separate months, therefore we need to 'stack' these on top of each other by using the wildcard union feature within the input. In this case we want to include all fields, therefore we can leave the matching pattern blank: 

Next, we can merge some fields so that we don't have null or duplicate fields. The following can be merged:

• Calls Offered + Offered
• Calls Not Answered + Not Answered
• Calls Answered + Answered

You can merge fields together by dragging them onto top of each other within the Profile pane. 

Finally, we can use the Table Name and File Path fields to create a date. 

Within the File Path field, we can remove all Letters and Punctuation using the Clean functionality. This should leave us with just the numbers (2021). From here we can use the following calculation to create the Month field: 

Month 

DATE([File Paths] + '-' + [Table Names] + '-01')

At this stage the Metrics table should look like this: 

We can now combine our workflow with the Metrics table, by using an outer join where we return all of the values from our original workflow. Note, that some of the metrics don't match therefore we need to use the outer join to return all of our rows. 

The join condition is where ID = Agent ID and Month Start Date = Month

After the join our table should look like this: 

Step 4 - Combine Goals

The final input that needs to be combined is the table containing information about Goals. Before join the table we need to make some changes to reshape the data slightly. 

First, we want to extract the number from the above/below text in the Goals field. The number is always the last single digit at the end of the string, so we can use the Right function to extract this: 

Goal Value 

INT(RIGHT([Goals],1))

Then we can need to create the join calculation (1) that we used earlier within the workflow. We'll need this for the join later!

Before the join, we want to change the shape of our table so that each of our Goals is in a separate field. Therefore, we can use a rows to columns pivot where the pivoted values is the Goals field and Goal Value is the aggregate field.

Our Goals table should now be a single row that looks like this: 

We are now ready to join the Goals to our workflow using the Join field that we created earlier: 

Our table now looks like this: 

Step 5 - Final Tidy

The last step this week is to give the table a final tidy so that it matches our desired output. First we want to round some of our numbers: 

Not Answered Rate 
ROUND([Calls Not Answered]/[Calls Offered],3)

Agent Avg Duration
ROUND([Total Duration]/[Calls Answered],0)

Then we want to calculate whether or not we met the various goals:

Met Sentiment Goal 
[Sentiment] >= [Sentiment Score >= 0]

Met Not Answered Rate 
[Not Answered Percent < 5] > ([Not Answered Rate]*100)

After these calculations we should be ready to output our data: 

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