2023: Week 27 - Solution
Solution by Tom Prowse and you can download the workflow here.
The challenge this week comes from Tanbir Jalil and is looking at helping the executives of the School District Board to analyse how their overhead costs vary from school to school.
Step 1 - Input Data
First we want to input all of our data. This is spread across multiple different sheets for each of the schools and we want to combine these into a single table. As the tables have the same structure we can use a wildcard union to 'stack' the tables on top of each other straight from the input step.
Within the input step we can select the 'Union Multiple Tables' option and then ensure that we have a matching pattern of 'School*' within the worksheet name. Using the '*' acts as a wildcard, therefore it will pick up all of the tables with 'School' in the title and not worry about the letters afterwards.
We can then remove the Table Names and File Path fields and the table should look like this:
Step 2 - Monthly Costs
We now want to calculate the monthly costs for each category and school. To do this we first need to create a date field from the Month and Year fields.
Date
[Month]+str([Year])
We can then change the field type to a Date field. This should then give us a correctly formatted date with 01 as the day and also the month string to be transformed into a month number.
After removing the Month and Year fields we can then change the shape of the table so that we have a column for each Cost name. Here we want to use a Rows to Columns pivot where we pivot the Name field and then aggregate the Sum of Value:
After the pivot the table should look like this:
Step 3 - Total Costs & Sort
Finally we want to calculate the total costs for each school:
Total Cost
[Gas Cost]+[Electricity Cost]+[Water Cost]+[Maintenance Cost]
Then we want to sort the table by month for each school, we can use the Rank calculation for this:
Sort
We are then ready to output our table that should look something like this:
You can download all the output from here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @tanbir_jalil, @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!
