2023: Week 5 - Solution

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

We move into our first intermediate month challenge this week, and we are looking at extending the functionality of Tableau Prep by introducing analytical calculations. If you are a Tableau Desktop user then you will be familiar with Level of Detail (LOD) calculations and we'll utilise these to make the end user's life a little easier by doing a couple of calculations for them.

Step 1 - Bank Code & Month Name

First we need to extract the Bank from the Transaction Code field. From the requirements we know that the letters within Transaction Code make up the Bank field, therefore we can use a custom split to extract anything before the first '-': 

From here we can rename the new field 'Bank': 

We can also extract the month name from the Date field. Again, we can use the in-built functionality within Tableau Prep to help us out here. From the Transaction Date, we can choose Convert Date and select Month Name 

After this our table should now look like this: 

Step 2 - Total & Rank

The next step that we need to do is calculate the total transaction values per bank and month. To do this we can use an aggregate step where we group by Transaction Date and Bank, then Sum the Value field:

Now we have the total for each month and bank combination we can rank each bank on their transaction values each month vs other banks. 

This is where we can use our first analytics calculation in the form of a Ranking.

You can write out the Rank function within a calculated field by utilising the partition and order by, but you can also use the ui to help out. 

From here we can group by Transaction Date, then rank the Value in a descending order: 

This will create the ranking for us and our table should look like this: 

Step 3 - Monthly and Bank Averages

Finally, we want to calculate the average rank per bank and the average transaction value per bank without losing all of the other data. Normally, we would use an aggregation to do this, however this would limit the detail within our view and we don't want that... This is where LODs come in!

We want to create an LOD for each Avg calculation that we require, and this can be done by typing out into a calculated field (similar to Tableau Desktop) or use the ui like we did with the rank above: 

Avg Rank per Bank

Avg Transaction Value per Rank

These two calculations give us the two averages that we require, and you'll notice that these have been created whilst maintaining the original level of detail.

Our final table should look like this: 

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