2023: Week 3 - Targets for DSB
Challenge by: Jenny Martin
For the third week of beginner month, we're going to be building on the skills that we've already learnt, as well as exploring new concepts. This week may feel a little more challenging, but I promise you're ready for it!
Data Source Bank has some quarterly targets for the value of transactions that are being performed in-person and online. It's our job to compare the transactions to these target figures.
Inputs
- Input the data
- For the transactions file:
- Filter the transactions to just look at DSB (help)
- These will be transactions that contain DSB in the Transaction Code field
- Rename the values in the Online or In-person field, Online of the 1 values and In-Person for the 2 values
- Change the date to be the quarter (help)
- Sum the transaction values for each quarter and for each Type of Transaction (Online or In-Person) (help)
- For the targets file:
- Pivot the quarterly targets so we have a row for each Type of Transaction and each Quarter (help)
- Rename the fields
- Remove the 'Q' from the quarter field and make the data type numeric (help)
- Join the two datasets together (help)
- You may need more than one join clause!
- Remove unnecessary fields
- Calculate the Variance to Target for each row (help)
- Output the data
Output
- 5 fields
- Online or In-Person
- Quarter
- Value
- Quarterly Targets
- Variance to Target
- 8 rows (9 including headers)
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!