2024: Week 6 - Solution
Solution by Tom Prowse and you can download the workflow here.
The challenge this week looks at assessing the different income tax bands within the UK. Within the flow we want to assess our members of staff salary and see what band they fall into for the year.
Step 1 - Latest Date
We only want to keep the latest figures for each of our members of staff, and sometimes they appear multiple times within our list.
First, we want to make sure that Tableau Prep includes a row number within our table. We can add a row number by activating the auto-generated field within the input step. Just right click and choose 'include field':
From here we want to identify the latest row for each member of staff. There are a couple of ways to do this, we could utilise a Fixed LOD, but in this case we are using an aggregate tool to find the Max Row Number for each Staff ID:
Then we use this max row to join back to our original data table using an inner join on row ID to only retain the rows that match with the max row ID:
At this stage our table should look like this with 803 rows:
Step 2 - Total Salary
Next we want to transform the shape of our data so that we have a column for each month and the value. For this we need to pivot our data and bring each of the columns into rows.
We utilise the Columns to Rows pivot with each of our numbered month columns in the pivot values:
This creates a long and thin table which looks like this after we renamed some fields:
It is now much easier to calculate the total salary by using an aggregation step, instead of writing out all of the different months in a long calculated field.
To calculate the total salary we need an aggregation tool where we group by Staff ID and then sum the Monthly Salary:
Step 3 - Tax Rates
Now we have the annual salary we can start to create the different tax bands for each member of staff.
After renaming the Monthly Salary field to Salary, we can first calculate the Max Tax Rate for each staff member. This will be the tax band that they fall into:
Max Tax Rate
IF [Salary] <= 12570 THEN 'No Tax'
ELSEIF [Salary] <= 50270 THEN '20% rate'
ELSEIF [Salary] <= 125140 THEN '40% rate'
ELSE '45% rate'
END
Next we can calculate how much tax they pay within each of the 3 bands:
20% Rate Tax Paid
IF [Max Tax Rate] = '20% rate' THEN 0.2*([Salary]-12570)
ELSE (50270-12570)*0.2
END
40% Rate Tax Paid
IF [Max Tax Rate] = '40% rate' THEN ([Salary]-50270)*0.4
ELSEIF [Max Tax Rate] = '45% rate' THEN (125140-50270)*0.4
END
45% Rate Tax Paid
IF [Max Tax Rate] = '45% rate' THEN ([Salary]-125140)*0.45 END
We have now identified how much tax each person will pay within the given tax band.
Total Tax Paid
ZN([20% rate tax paid])
+ZN([40% rate paid])
+ZN([45% rate tax paid])
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!
