This week we looked at a classic problem for anyone who is lucky enough to own property... how much is left on a property mortgage? We want to do this for each of our stores so lets look at how we can solve the challenge.
Step 1 - Months to Repay
The first step is to calculate how many months we have remaining to repay the mortgage in full. This consists of a few calculated fields as we need a start date, how much is being paid, and then to calculate the number of months until the end date.
Today
MAKEDATE(2022,8,10)
This creates a 'today' field based on 10th August 2022. In a real life situation we could just use the TODAY() function as this will update based on the actual date.
Monthly Capital Repayment
[Monthly Payment]*([% of Monthly Repayment going to Capital]/100)
This calculates how much capital is being paid off each month.
Months to Repay
CEILING([Capital Repayment Remaining]/[Monthly Capital Repayment])
This calculates how many months are remaining based on how much the monthly payments are. We have rounded this up using the ceiling function.
Then using the above calculations we can calculate the expected date that we will repay the mortgage in full.
End Date
DATEADD('month',[Months to Repay]-1,[Today])
We need to make sure this is a date field as well.
At this stage our table should look like this:
Step 2 - Row for Each Month
Next we want to create a row for each month that the mortgage will run for (from today to end date). We can use the New Rows functionality within Tableau Prep to help with this.
Within the setup of the New Rows, we want to add rows from a range of two fields, where Today <= End Date, each increment is 1 month, and the values should be copied from the previous row.
After creating the rows we can remove the additional fields that we now longer need so we should be left with a table that has Store, Monthly Capital Repayment, Capital Repayment Remaining, and Monthly Payment Date:
Step 3 - Amount Remaining
Now we have a row for each month with the monthly payment, we can use this to calculate how much is remaining for each of the stores and also overall.
The next step is to use a self-join so that we can do a running total based on each of the months. First we want to create a new clean step, then we can use this to join back to the step before using an inner join where Monthly Payment Date (from the new Clean Step) >= Monthly Payment Date (from the original field, we can rename this duplicate date so we don't get confused).
Our workflow should look like this:
And the join should look like this:
Notice how the number of rows has massively increased due to use join on all future months. This can be solved by using an aggregation to bring this back to a single row per month, and also calculating the running total.
In the aggregation we want to group by Monthly Payment Date & Store, then Sum Monthly Capital Repayment and then Avg Capital Repayment Remainder.
After the aggregation we should be back down the 613 rows and our table should look like this:
Step 4 - Remaining & Outstanding Capital
The final steps for this challenge is to calculate the Remaining Capital to Repay and the Capital Outstanding Total. We can do this by using the following calculations:
Remaining Capital to Repay
[Capital Repayment Remaining]-[Monthly Capital Repayment]
As our table is now set up in the correct way, we can just subtract one from the other.
Capital Outstanding Total
We used a fixed LOD here as we are working across both stores, so need to sum both months together whilst also maintaining the granularity of the table.
After removing the unneeded fields, we should now be ready to output our data that looks like this:
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!
Created by: Carl Allchin Welcome to a New Year of Preppin' Data. These are weekly exercises to help you learn and develop data preparation skills. We publish the challenges on a Wednesday and share a solution the following Tuesday. You can take the challenges whenever you want and we love to see your solutions. With data preparation, there is never just one way to complete the tasks so sharing your solutions will help others learn too. Share on Twitter, LinkedIn, the Tableau Forums or wherever you want to too. Tag Jenny Martin, Tom Prowse or myself or just use the #PreppinData to share your solutions. The challenges are designed for learning Tableau Prep but we have a broad community who complete the challenges in R, Python, SQL, DBT, EasyMorph and many other tools. We love seeing people learn new tools so feel free to use whatever tools you want to complete the challenges. A New Year means we start afresh so January's challenges will be focused on beginners. We will use dif
Challenge By: Jenny Martin For week 2 of our beginner month, Data Source Bank has a requirement to construct International Bank Account Numbers (IBANs), even for Transactions taking place in the UK. We have all the information in separate fields, we just need to put it altogether in the following order: Inputs A list of the transactions, with information about the receiving bank account A lookup table for the SWIFT Bank Codes Requirements Input the data In the Transactions table, there is a Sort Code field which contains dashes. We need to remove these so just have a 6 digit string ( hint ) Use the SWIFT Bank Code lookup table to bring in additional information about the SWIFT code and Check Digits of the receiving bank account ( hint ) Add a field for the Country Code ( hint ) Hint: all these transactions take place in the UK so the Country Code should be GB Create the IBAN as above ( hint ) Hint: watch out for trying to combine sting fields with numeric fields - check data types Re
Free isn't always a good thing. In data, Free text is the example to state when proving that statements correct. However, lots of benefit can be gained from understanding data that has been entered in Free Text fields. What do we mean by Free Text? Free Text is the string based data that comes from allowing people to type answers in to systems and forms. The resulting data is normally stored within one column, with one answer per cell. As Free Text means the answer could be anything, this is what you get - absolutely anything. From expletives to slang, the words you will find in the data may be a challenge to interpret but the text is the closest way to collect the voice of your customer / employee. The Free Text field is likely to contain long, rambling sentences that can simply be analysed. If you count these fields, you are likely to have one of each entry each. Therefore, simply counting the entries will not provide anything meaningful to your analysis. The value is in
