Preppin' Data

Challenge by: Jenny Martin Prep Air would like to do some analysis on how their flights are filling up over time. They've given a small sample of flights that will be taking off next month, and the actions that customers who have booked those flights have been taking.  Inputs A customer actions table in which a new row appears each time a customer takes an action relating to their flight booking  A flight details table detailing how many seats are available for each class on the flight  Requirements Input the data If the customer has cancelled their flight, make sure all rows are filtered out for that flight  For each customer on a flight, filter the dataset to their most recent action Based on the Date field, create a field which shows how many seats in total have been booked as of that date for each flight and class Hint: Running Sum could be useful here! Bring in information about the Flight Details Calculate the Capacity %: of the available seats on the flight for each class, w

Solution by Tom Prowse and you can download the workflow here . Step 1 - Prep Air Loyalty Scheme The first task of this week's challenge is to filter to keep the customers who are part of the loyalty scheme. One of the conditions is that you have to have flown since the 21st Feb 2023 so we need to filter the Customers table to show only customers with a flight after this date:  [Last Date Flown]>=date('2023-02-21') Next we want to categorise the customers into different tiers based on the number of flights they have taken. For this we want to be able to control the bucket size by using a parameter so that the user can select either of the options.  The parameter needs to be a whole number which has a list of values where the user can select 5 or 10:  We can then use this parameter to create the tiers that each customer falls into:  Tier FLOOR([Number of Flights]/[Parameters.Tier Bin Size]) Then finally we can calculate how many flights a customer takes a year, on average

Challenge by: Jenny Martin For this week's challenge, Prep Air have asked for some What If? Analysis. They're considering 2 different systems for rewarding customer loyalty and want to understand how that might impact cost and how many customers might benefit from the program. Inputs Prep Air Customer Details - the team were able to add additional information to Week 5's input  Prep Air Loyalty schemes  Prep Air Loyalty Costings  Requirements Input the data To be part of either Prep Air Loyalty Scheme, Customers must have flown in the last year (on or after 21st February 2023) Create a parameter so that the number of flights a customer has taken is either bucketed into groups of 5 or groups to 10 e.g. if the parameter selected is 5, the groupings will be 1-4, 5-9 etc if the parameter selected is 10, the groupings will be 1-9, 10-19 etc Create a field to categorize customers based on the selected parameter, called Tier Estimate the average number of flights a customer takes

Solution by Tom Prowse and you can download the workflow here . The challenge this week is a themed one all about Valentines day! We want to match up some couples and what gifts they receive based on the length of their relationship.  Step 1 - Number of Valentines First we want to use the Couples input to calculate how many valentines days a couple has been together. After inputting the data we can make sure that the Start date has the correct data type - from a string to a date type. Then we can create a Valentine's day for 2024 by creating a new date field:  Valentine's Day 2024 MAKEDATE(2024,2,14) Now we have these two dates we can then create a new row for each of the Valentine's by using the New Rows step:  Then we can filter for only Valentine's days by filtering for February and the 14th:  (MONTH([NewRows1]) = 2) AND (DAY([NewRows1]) = 14) Then use an aggregation to count the number of rows per couple:  At this stage our table should look like this:  Step 2 - Com

Challenge by: Jenny Martin We'll take a break from helping Prep Air for this challenge, but don't worry, it's for a good cause! We've got a number of couples who want some inspiration for Valentine's Day gifts (since this challenge is being released on Valentine's Day!) We want to count the number of Valentine's Days that the couples have been together for, so they can get inspired on how to theme their gifts this year. Inputs A list of the couples and when their relationships began. Gift ideas based on the length of time they've been together (based on this website ) Requirements Input the data Fix the Relationship Start field so that it has a Date data type Create a field for today's date (14/02/2024) To count the number of Valentine's days, we need to think a little more creatively than using a simple datediff function to count the number of years. A couple of potential routes could be: Scaffolding the data so there is a row per day, filtering

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

 Created by: Carl Allchin Welcome to the first week of the Intermediary level challenges for 2024. This means we'll leave more space for you to work out the logic and be less specific about the techniques you are likely to need.  The end of January in the UK (where Prep Air is based) is when residents have to submit their income tax returns by. To help our team, we've offered to summarise their tax position for them. The UK income tax works by bands. Here's a summary table showing the percentage of tax for each pound earned in that bracket:  For example, if I earned £12,571. I would pay £0.20 of tax in total: £0 for the first £12,570 earned and then 20% of the £1 in the next tax band.  Input One csv file containing the monthly salary for staff. If any team member has a change in their pay, their new salary is recorded as a later record but the input contains their former record based on what they would have been paid Requirements Input the csv file Add a row number to the

Solution by Tom Prowse and you can download the workflow here .  This is the final week of our beginner month and we are focussing on joins and the different types of conditions that we can create from them. Step 1 - Output 1 After inputting all of our data sources, we are first going to focus on creating the first output from the requirements. For this we first need to create a table that includes information about the Ticket sales and customers. To get this information we join together the Ticket Sales table with the Customers table using an inner join on the Customer ID field We then want to include the information about each of the flights, so we can combine the Flights table by using another inner join on the Date and Flight Number fields:  At this stage we'll have some fields that have been duplicated from the join so we can remove these and our final table for the first output should look like this:  Step 2 - Output 2 For the second output we want to go right back to the sta