Preppin' Data

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

  Solution by Tom Prowse and you can download the full workflow here .  We continued our fundamental challenges this week with a focus on string calculations. We are trying to find out some more detail on what is going on with the project over runs in Prep Air by parsing out the key information from the commentary log.  Step 1 - Create Week The first step that we have done this week is to create a 'Week X' field. We have already been provided with the week number so we need to add the string 'Week' before each one. We use the following calculation for this:  Week 'Week '+ str([Week]) Our week rows now look like this:  Step 2 - Split Commentary The next step is to break apart each of the different projects from the commentary field. To do this we want to split each one into a separate row by using the split functionality. Each project/task starts with a pair of square brackets (eg, [NLS/Op-Sc]) so we can use the '[' to split each of the different projects...

 Challenge by: Carl Allchin Continuing on our fundamental skills theme in May, this week's challenge is all about string calculations. If you want to know more about what String data is and how you might want to work with it, you might want to check out our how to... deal with strings post. Pay particular attention to split, right, mid and find if you're getting stuck with this challenge! I've written another guide to the common functions that you might find use when working with Strings in Tableau.  The Challenge This week we are trying to find out more detail on what is going on with the project over runs in Prep Air (every data prepper's favourite airline). To get more detail than just what was shared last week we've uncovered the commentary log that sits behind our project management system. Like any system that holds the detail shown on the programme's interface in a log file, it has great detail but held in an unfriendly way.  We need your help to get st...

Solution by Tom Prowse and you can download the full workflow here .  For the month of May we are going to be building on the challenges that we released back in January. In January we focussed on some fundamentals that would help people get started with Preppin' Data without jumping into some of the more challenging tasks. This week we are focussing on Dates and how we can prepare the data to create a Gantt chart for our dashboard in Tableau Desktop. Here's how we solved it! Step 1 - Calculate Completed Date After inputting the data, we want to create a clean step where we can create a calculated field to calculate the completion date for each of our tasks. Completed Date DATEADD('day',[Completed In Days from Scheduled Date],[Scheduled Date]) We can then can the data type to a Date, and rename the 'Completed In Days from Schedule' to 'Days Difference to Schedule'.  Our table now looks like this:  Step 2 - Pivot Task The next task is to transform the sha...

 Challenge by: Carl Allchin In January we ran a month of challenges that focused on letting people try Preppin' for the first time without jumping into some of the more challenging functions. This month, we want to build on that knowledge with a set of beginner / intermediate challenges. We'll keep going with the help links but clearly flag up the focus of the challenge.  If you haven't tried the January challenges but wonder if Preppin' might be too hard (it won't be), please go back and try these challenges. You will find these challenges and solution posts here . The Challenge This week's challenge is focused on Dates and the calculation functions available to you. Here's a recent blog post that I wrote that might help you if you want a little extra support. This week we would like you to prepare you data for building a Gantt chart and supporting information on a dashboard (you don't have to build the dashboard but bonus points if you do!). Prep Air...

  Solution by Tom Prowse and you can download the workflow here .  This week's challenge came from Pat Lucas and we looked at a classic data preparation challenge based around timesheets. There were lots of detailed requirements in this one so let's see how we solved it! Step 1 - Remove Totals After inputting the data set, we first want to remove any rows that contained a total in the Project field. This is nice and easy as we can select the three rows ('Overall Total for Dan/Sam/George') and then right-click and exclude them from our workflow.  Step 2 - Pivot Dates Next we want to transform the structure of our table, and move all of the dates into a single column by using a Columns to Rows pivot. We have used the 'Wildcard Pivot' on a '/' to automatically bring in all of the date fields:  Step 3 - Split Name, Age & Area We are now ready to split the Name, Age, Area of Work field into separate columns. We can't use a single custom split in this...