2023: Week 50 - Solution
Solution by Tom Prowse and you can download the workflow here.
This week we continue from the previous challenge and take our analysis a little bit further.
Step 1 - Create Parameter
First we want to introduce a parameter so that the user can control the input value that they want. For this we want it to be a list of values from 50 to 500 in increments of 50
Step 2 - Max Monthly Deposit
We can now start using the parameter within our previous workflow. First we want to identify how the value in the parameter compares to the monthly max deposit from the supplier. This can be input into the existing workflow by adding some additional steps between the existing steps:
For this we can create a new calculated field that includes the parameter value, then we can use a MIN function to identify the minimum value between the parameter and max monthly deposit.
Monthly Deposit
MIN([Customer Planned Deposit],[Max Monthly Deposit])
Then we can calculate what is 'leftover':
Leftover savings which won't receive interest
[Customer Planned Deposit]-[Monthly Deposit]
Note, we are working with the parameter value of 300, so at this stage our table looks like this:
Step 3 - Add Leftovers
After we have expanded the number of rows we can then add back in the leftovers which won't have earned any interest. The step after we calculate the interest we can update the 'Apply Interest' calculation:
Apply Interest
[Apply Interest]+[Leftover Savings which won't receive interest]
Once we have added the additional savings back to the overall savings amount, we can add the parameter name into the output name so we can clearly see how much has been selected.
When selecting 300 in the parameter our 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!
