Preppin' Data

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 ...

 Challenge by: Carl Allchin Our final challenge for calculations month is all about the Analytical Calculations in Tableau Prep, Level of Detail calculations and Rankings. These calculations let you answer the questions your stakeholders have before you've even visualised anything. Sometimes all we need is the answer. If you're not using Prep for this challenge, I have hopefully made a challenge you can replicate too (you might just need to use a join).  Challenge With the Prep Air - New Trolley Inventory project finally delivered at the end of May, we want to analyse what are the products that we are now selling for a much higher amount than we did before the project. We want to analyse the top three products based on price rise per destination.  Input One spreadsheet with 10 sheets, one sheet per month (Jan is month 1, Feb is month 2 etc) Requirements Input data Bring all the sheets together Use the Day of Month and Table Names (sheet name in other tools) to form a dat...

Solution by Tom Prowse and you can download the full workflow here .  Continuing the calculations theme for May, this week we looked at numbers. The aim was to make creating a control chart in Tableau Desktop a lot easier by doing some of the table calculations in Prep before bringing the data into Desktop.  Step 1 - Create Mean & Standard Dev The first task is to create the mean and standard deviation for the complaint field. To do this we want to duplicate the complaints field, as we want to use this twice in the next aggregation step.  The aggregation step is where we are going to create the mean and stdev for each week. Therefore, we group by Week and calculate the Avg and Stdev for our two complaints fields: Step 2 - Upper & Lower Control Limits Now we have the mean and stdev, we can calculate the upper and lower control limits for our chart. These are calculated by adding or subtracting the stdev from the mean: Upper Control Limit [Mean] + [Standard Deviatio...

 Challenge by: Carl Allchin This week's challenge continues the focus on calculations, this time the focus is numbers. When using measures in data it is very easy to make mistakes if you don't check the realistic nature of the values, especially when entering data or forming calculations. By creating your calculations in your data preparation tool, you might be saving the users of your data set a lot of work and reducing the skills required to use the data.  Challenge Control charts are a really useful way to visualise data but in Tableau Desktop they often require a few Table Calculations putting people off creating them. This week you will be building the calculations you need to build a control chart without using table calculations in Desktop.  Different people like to use differing numbers of standard deviations to assess whether the data point falls outside of the normal levels of distribution or not. Some people class normal distribution as: 1 standard deviation ei...