2023: Week 49 - Solution
Solution by Tom Prowse and you can download the workbook here.
Step 1 - Update Fields
First we want to make the Max Monthly Amount field numeric so we can update it using this calculation:
Max Monthly Amount
REPLACE([Max Monthly Deposit],'£','')
We can then change the data type to a whole number.
Next, we want to update the Provider field so that the additional conditions are easily identifiable. Here we can use an IF statement to update the provider field:
Provider
IF [Has Additional Conditions]='Y'
THEN [Provider]+" (Conditions Apply)"
ELSE [Provider]
END
At this stage the table should look like this:
Step 2 - A row for each month
Next we want to create a row for each month that we will be using the account. For this we need to create two date fields and then use these to pad out the additional rows.
1st Month
MAKEDATE(2024,1,1)
12th Month
MAKEDATE(2024,12,1)
Then we can use the New Rows step to pad out the additional months:
Finally, we can create a month number by duplicating the Months field and converting the date to a month number.
We can then remove and rename the additional months fields so that our table look like this:
Step 3 - Savings Value
Now we can calculate the interest that will be earned on the savings during the period. First we can apply the interest:
Apply Interest
[Max Monthly Deposit]*POWER(1+([Interest]/12),[Month])
This is following the equation given in the requirements around calculating compound interest.
Then using the interest value, we can calculate the savings each month for each provider using a running total:
Savings each month
After rounding this value to 2 decimal places (ROUND([Savings each month],2)) our table should look like this:
Step 4 - Best Deal
We can now start to rank the different providers based on what is the best deal.
First we can calculate the maximum total interest for each provider using a fixed LOD:
Then we want to calculate the total interest that can be earned:
Total Interest
ROUND([Max Possible Savings]-([Max Monthly Deposit]*12),2)
Then finally rank and sort the table from best to worst.
Rank by Max Savings
Providers Ranked by Total Interest
Finally to sort the table by month and best deal for total interest, we can use another rank field and then hide this field so that it doesn't show in the output.
Sort
After we've hidden the sort field our final output should look like this:
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!
