Preppin' Data

Solution by Tom Prowse and you can download the  workflow here .  The challenge this week was to look at managing unscheduled time off at Chin & Beard Suds Co. In the challenge we are looking to analyse how many people have been off and if we have had someone off every day.  Step 1 - Build Out Dates The first part of the challenge is to build out our data set so that we cover every date listed between 1st April and 31st March. This is where the scaffold will come in useful as we can use this to fill in any dates that we might be missing in our Reasons data set.  To make sure we are starting on the correct date, we first need to find the first date across our Reasons table. We can do this by using an aggregate tool, and finding the Min Date:  We now want to join this to our scaffold. To do this we need to do a cross join so that each of the rows from table is joined with all of the rows from another. Therefore, we need to create a calculated field to do this, I've just a 1 but t

 Challenge by: Carl Allchin Chin & Beard Suds Co is just like any other company, people have unscheduled time off. Whilst this is expected in organisations, it can be difficult to manage. At C&BS Co, we have had a rough start to our financial year with lots of people being off for illness or sickness. How bad has it been and do we have people off every single day? This analysis can be tough in BI tools to look at the day-to-day reality when days off are recorded with just a start date and the number of days taken off. This week's challenge is producing a simple data set that will give us this view.  We are analysing the period 1st April to 31st May 2021. Input One Excel workbook , two sheets: 1. Absence Table 2. Scaffold Requirements Input data Build a data set that has each date listed out between 1st April to 31st May 2021 Build a data set containing each date someone will be off work Merge these two data sets together  Workout the number of people off each day Output the

  Solution by Tom Prowse and you can download the workflow here .  We are looking to improve our service on Prep Air so want to calculate NPS from a survey and see how we compare.  Step 1 - Combine Data First we want to combine the data from both of the files. We could use the union tool for this, but I have used the Wildcard Union in the input tool. You don't need to add any matching patterns as we want to bring all of the files through: We should now have both of our inputs in a single table: Step 2 - Classify Customers Next we want to classify the responses that we want to compare, so the first step is to count the total number of customers for each Airline. To calculate this we can use a fixed LOD:  Number of Customers After counting the customers, we can then filter to only airlines with more than 50 customers. You can filter this by using a range filter on Number of Customers:  Finally, to classify the customers responses we can use the following IF statement:  Classification

Challenge by: Jenny Martin This week Prep Air are looking into their Net Promoter Score (NPS) and how this compares with a variety of other new airlines. NPS usually takes the form of asking customers "How likely ae you to recommend this company on a scale of 0-10?" You then subtract the detractors of your company from the promoters and end up with a score between -100 and +100. The higher the NPS, the better! However, like most metrics, on its own it doesn't tell you a lot. Do customers feel strongly one way or the other about any airlines? So it would be good to compare Prep Air's NPS with other airline's Net Promoter Scores too! In this challenge we'll use Z-Scores to standardise the scores and see whether Prep Air is above or below average. Inputs 1. Prep Air's customer ratings: 2. Other Airlines customer ratings Requirements Input the data Combine Prep Air dataset with other airlines Exclude any airlines who have had less than 50 customers respond C

  Solution by Tom Prowse and you can download the workflow here.  This week's challenge was a bit of fun where we looked to try and replicate on of the games from the UK TV show 'Richard Osman's House of Games'. We were trying to replicate the final round called Answer Smash, where you have to combine a picture with a question to get your answer.  We have tried to replicate this with a few of the Preppin' Data regulars and some different categories linked to their names. Lets look at how we solved the challenge! Step 1 - Clean Category Field First up we need to clean the category field by splitting apart the two parts; Category & Answer. These are split by a ':' and we can use an automatic split to bring these into two columns. After the split we can rename the fields and remove the original so that our table looks like this:  Step 2 - Join Data Sets The next step is to combine our data sets together by joining them. First we want to join the Answer Sma

Challenge By: Jenny Martin Recently, my family and I have become quite invested in the TV quiz show Richard Osman's House of Games . The final round is always a round called Answer Smash. In this round you have a picture and question and you have to "smash" the name of the picture with the answer to the question, as per the below example. I thought it would be fun to work backwards and have a list of answer smashes from which we have to extract the Preppin' participant and the answer to the question! Inputs 1. Answer Smash list 2. Names 3. Questions 4. Categories Requirements Input the data The category dataset requires some cleaning so that Category and Answer are 2 separate fields ( hint ) Join the datasets together, making sure to keep an eye on row counts ( hint ) Filter the data so that each answer smash is matched with the corresponding name and answer ( hint ) Remove unnecessary columns Output the data Output 5 fields Q No Name Question Answer Answer Smash 20 r

Solution by Tom Prowse and you can download our workflow here .  This was our final challenge for the month of May and our calculations month! We looked at level of detail, ranks and using some of the other calculations that we have touched on throughout the month.  The challenge was to analyse what products are selling from Prep Air's on-flight trolley at a higher amount than before and to see what the top 3 products are, based on their price rise. Step 1 - Input Data  The first step is to input our data. We only have a single data source, but there are multiple tables within this file, therefore instead of bringing in each of these individually we are going to use the wildcard input.  To do this, bring in one of the tables then select 'Multiple Files' from the input tool. Next select 'Wildcard Union' and then we want to bring back all files that start with the word 'Month'. Step 2 - Create Date Now we have all of the tables in a single input, we can start