2023: Week 3 - Solution

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

We continued our introductory month based around the Data Source Bank. This week we looked at including targets and combining the data sources to extend our analysis.

Step 1 - Transactions File

First we want to prepare the Transactions file input so that it is ready to combine with the Targets table. 

After inputting the data we need to filter the table so the transaction code only contains values with 'DSB' in them. For this we can use a 'Wildcard Match' filter: 

Next we want to update the Online or In-Person field from a number to a string. Once it is a string, we can then rename the fields where 1= Online and 2=In-Person. 

Then we want to extract only the Quarter from the Transaction Date field. To do this we can make use of the in-built functionality within Tableau Prep that includes converting dates: 

Finally, we want to find the total Value by Quarter and Online or In-Person so we need to use an aggregation 

After the aggregation our transactions table is ready and should look like this: 

Step 2 - Targets

Now we want to connect to our second data source that contains the information about the Targets. The targets table also needs some transformations before we are able to combine it with the transactions. 

First, we want to make sure that they are in the same structure. Currently, the Targets table is a wide table, with each of the quarters in a separate field. This isn't ideal for when we work with Tableau or to combine with the Transactions table, therefore we want to pivot so that the quarters are in a single field and the table becomes long.

For the pivot we can use a Columns to Rows pivot with each of the Quarter fields, we can drag these in separately, or use the Wildcard Pivot on the letter 'Q':

After pivoting we can rename Q to Quarterly Targets and Pivot1Names to Quarter.

Then finally we want to clean the Quarter field to remove the Q, so that this field matches the Quarter field in our Transactions table. To do this we want to remove letters, then change to a whole number. 

Out Targets table should look like this: 

Step 3 - Combining & Variance

Now we have both tables in a similar shape, we can combine them using a join. In order to correctly match up the two tables we want an inner join where Online or In-Person = Online or In-Person and where Transaction Date = Quarter:

 

We can then remove the duplicated fields (Transaction Date & Online or In-Person-1), and then finally calculate the variance between the actuals and targets: 

Variance to Target
[Value]-[Quarterly Targets]

After this calculation we are ready to output our data:

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!