Preppin' Data

Challenge by: Jenny Martin It's always been interesting to me how reporting dates can be so different for different industries. There is often a table that is centrally maintained explaining how the reporting date translates to the calendar date. This way any dataset can be easily enriched with the reporting dates.  This challenge will be all about creating a dynamically updating reporting date table. Imagine a company uses the following logic: Their reporting year begins on the Monday before 1st February e.g. In 2023, 1st February fell on a Wednesday. Therefore the first day of the reporting year will be 30th January Each Reporting Month contains exactly 4 Reporting Weeks This is the logic we will use for this challenge, but you can imagine how it could be easily updated to fit another organisation's logic. Input The input this week is very simple. We'll update this value so the user can choose a calendar year to generate the reporting dates for. Requirements These will be...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Term Data First we need to bring all of the data from the Term tables into a single sheet. We can do this by using the wildcard union within the input step that will allow us to bring the multiple sheets in and combine them together. Within the union we need to ensure that the worksheet filters contain the matching word 'Term*' so that it brings all tables with the word term in from the data source:  Step 2 - Parse Dates Next up we can start to parse and format the date field so that we can create it as a date data type. First we can duplicate the Date and rename to Term Date, then to make sure we have a consistent pattern we can replace any '/' with '.':  Term Date REPLACE([Term Date],'/','.') We can then change this to a Date data type which will convert some of the dates, but some dates don't have a day associated with them so we need to handle these in a different wa...

 Challenge By: Ed Hayter This is the conclusion of our Prep School challenges from DS38. Thanks for all your hard work team!  This Preppin’ Data Challenge requires you to process student grades. The Prep School records this information in one excel sheet, with a sheet for each term the report comes from. The reports are in a fairly consistent format with 4 fields for the grades in each subject, but different date formats have been used. The school would like you to prepare two outputs: 1. A table with a student’s average grade (GPA)  across subjects for each term and then a calculated 3 term moving average of GPA to help identify struggling students. 2. A table with just the students who were awarded a prize, the date the award was issued, the 3 term rolling average of GPA that merited the award. Inputs 1 Excel File that contains 6 sheets for Terms 1-6 and a sheet for the Headmaster's Notes  1 csv file that provides a lookup for Student Names Requirements Input the d...

Solution by Tom Prowse and you can download the workflow here .  Step 1 - Minutes Late After inputting the data we want to remove any null values from the Arrival Time. We can do this by selecting the 'Null' value in the profile pane and then exclude. We can then create the Scheduled Start Time and Arrival Time by combining the date with each of the time fields. Scheduled Start Time MAKEDATETIME([Date],[Scheduled Start Time]) Arrival Time MAKEDATETIME([Date],[Arrival Time]) We can then remove the Date field and then calculate how late each student was by using a datediff calculation:  Lateness DATEDIFF('minute',[Scheduled Start Time],[Arrival Time]) At this stage our table should look like this:  Step 2 - Output 1 At this stage we want to split the workflow into separate branches - one for each output. In our first output we want to calculate the average lateness of students per weekday. We can calculate this using an aggregate step where we group by Day of Week and Avg...

 Challenge by:  Adrien Sourdille This challenge is the penultimate of the ongoing series related to “Prep” School provided by DS38. Over to Adrien to explain the challenge: As the Prep School principal, you have heard worrying reports about some of your students being constantly late to class. Naturally, you want to investigate these rumors and get to the bottom of what is happening in your establishment. Luckily, your pupils need to swipe their student cards to enter the building every morning. The attendance log you have access to describes the daily arrival time of 8 students for the year 2022. Null values are generated if the student is absent that day.  You are particularly interested in understanding on what day of the week your students are the latest and which students are “very late” (more than 5 minutes) the most often.  Input 1 input this week with card entry data Requirements Input data Exclude null values from the Arrival Time field. These are days ...

Solution by Tom Prowse and you can download the workflow here .  Step 1 - Project Name First we want to separate the project name from the financial transactions in the project field.  Project Name IF ISNULL([Cost])  AND ISNULL([Invoiced Amount])  THEN [Project]  END After bringing these project names out into a separate field, you can see that the field contains a lot of null values. This is where we want to create a fill down calculation to fill in the missing nulls.  Fill down calculations can be found in the ever growing 'Create Calculated Field' menu:  Like other calculations in Tableau Prep, this is also a visual calculation editor which will aid our understanding of how to set it up.  Working from left to right:  1. Group by - how shall we split the calculation. In our case we want to look over the whole table. 2. Order by - what field do we want to use as the sequence? We can utilise the Source Row Number to ensure that we are reading...

 Created by: Carl Allchin  With the release of Tableau Prep Builder 2023.3, a long awaited feature of Fill Down was finally added to the tool. This is a common task when working with file based data or outputs of non-data focused systems. Within 24 hours of the beta version was released, I had used the feature twice to save me hours of work and rework.  This challenge stems from the those tasks. I've mocked up the data but the data structure is real. It's based on a report that can be extracted from the accounting software called Xero.  I know lots of Preppers don't use Tableau Prep but thought the task would be a useful challenge as you to are likely to come across the challenge when working with the same inputs.  Input One Excel input  Requirement Input the data   Separate the Project Name from the financial transactions in the Project Field Financial Transactions have either Invoice or Expense in the name Fill in the field if there are any nulls...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Missing Dates First we want to input the Events table and then utilise the New Rows steps to fill in the missing dates between the first day of term and last day of term. The new rows step is setup with using the Date field between the min/max dates with an increment of 1 day: After the new rows have been created we want to find the weekday for each of the dates so we can duplicate the date field and then convert the duplicate to day of week Then we can filter to remove the weekend days (Saturday & Sunday). The table should now look like this:  Step 2 - After School Clubs Now we can include the After School Clubs data source and join with our existing workflow using the day of the week fields on both sides. Finally we can replace the null values in the Event field with N/A by double clicking on the profile pane and typing 'N/A'. Our final output looks like this: You can download the  output from here ...

 Created by: Zoe Reed This challenge is part of the ongoing series related to “Prep” School provided by DS38. The Prep School need to construct their calendar for the spring half-term. They have a table of important events in the spring half-term, however they need to fill out the rest of the calendar for dates 17/04/23-26/05/23. Input Two inputs: 1. Events 2. After School Clubs Requirements ·         Input the data on events ·         Use the new rows step to fill in all the missing dates between the First Day of Term and Last Day of Term. ·      The school would like a column containing the Day of the Week. ·       Remove weekends from the calendar as these are not relevant for school events. ·        Input the After School Club data set and combine the data sets to show which club is on which day ·       Update the nulls in the Event field with N/A. ·      ...