Preppin' Data

Challenge by: Jenny Martin This challenge was inspired by a recent use case from a client. It involves bringing together 2 datasets which have different levels of aggregation. They were initially trying to do this in Tableau Desktop but getting a bit muddled with the calculations going on "in the background." So let's use the power of Prep to map out the process and make everything much clearer.  Inputs Streaming sessions from users  Avg Pricing for each month  Requirements Input the data Check the location field for spelling errors Data roles may help you identify these Fix the date fields so they are recognised as date data types Aggregate the data to find the total duration of each streaming session (as identified by the timestamp) We need to update the content_type field: For London, Cardiff and Edinburgh, the content_type is defined as "Primary" For other locations, maintain the "Preserved" content_type and update all others to have a "Second...

Solution by Tom Prowse and you can download the workflow here . We're looking at restaurant orders this week and how we can change the structure of the input so that it's easier to understand for when the chefs come to cook up the food. Step 1 - Input Data  First up we need to input the data and get this in a nicer format so that we can combine the orders with the recipe lookup table.  In it's current form, the table of orders is a hard one for Tableau Prep to understand because we can't associate what has been selected (🗸) with who made that selection.  Therefore, we need to split the Orders table into 4 separate inputs (1 for each person) and in each of the inputs we only want 2 fields - the name and the selection. 4 input steps:  Example of the fields retained from Jenny's input:  Once we have input the 4 separate inputs, we can combine these using a union step and then merge the fields so that all of the names are in a single field and the selections are in...

Created by: Jenny Martin The Preppin' Data team are planning to go out to dinner. However, the system the restaurant has used to collect orders doesn't provide the chefs with the easiest way to understand what they should be making. To help things go swimmingly on the night, as well as for all other guests using this ordering system in the future, we'll create a workflow to only show the ordered dishes for each guest. Inputs Orders from Guests  Recipe ID lookup table  Requirements Input the data Reshape the Orders table so that we have 3 columns: Guest name Dish Selections (containing 🗸 or null) Extract the course name from the Dish field Group these so that Starter and Starters are treated the same, for example Fill down the course name for each Guest ( hint ) It may help to bring in the Recipe ID from the Lookup Table  Where the Dish contains the Course name, it may be helpful to replace the Recipe ID in the following way: Starters = 1 Mains = 2  Dessert = 3 ...

Solution by Tom Prowse and you can download the workflow here .  This week we looked at a property management challenge, where we wanted to calculate the amount of rental revenue that our company is making from it's portfolio. The challenge was created by Tom Smith ( @tSmithyyy ) so let's take a look at how to solve it.  Step 1 - Contract Length First we want to calculate the length of each contract in months. To do this we want to input the Rental Contracts data then use a DateDiff calculation to find the length of the contract in months:  Contract Length   DATEDIFF('month', [Contract Start], [Contract End]) Then we can calculate how many months are left until the contract expires: Months Until Expiry   DATEDIFF('month', MAKEDATE(2022,04,13), [Contract End]) Then finally we can find the month of the contract start and end month by using the following calculations: Contract Start Month DATE(DATETRUNC('month',[Contract Start])) Contract End Month DATE(DAT...

Created by: Tom Smith (Twitter: @tSmithyyy ) You work for a Property Management company and your boss has asked you to compile a report detailing how much revenue the business can expect to make from its existing office space rental contracts. The time frame in focus is from 2021 until 2026 and the board would like to see a cumulative total for each month of a contract. An additional requirement is to create a dynamic table that totals rent paid for completed years across all contracts, as well as year to date figures for the current year, which would update as time goes on. Input files: Rental Contracts Office Space Prices Requirements: Input the Rental Contracts data Work out the length of each contract in months Work out the number of months until each contract expires (imagine today is 13th April 2022) Input the Office Space Prices data and join it to the contracts table Remove duplicated fields Create a row for each month that a rental contract will be live - Retain the details f...

Solution by Tom Prowse and you can download the workflow here . This week we revisit one of Jenny's favourite TV shows, Richard Osman's House of Games, and look at if the results would be different if we changed the double points scoring system.  Step 1 - Keep Relevant Fields The first step after inputting the data is to only keep the relevant fields that are shown in the requirements. We can do this straight from the input step by using the tick boxes to select only the fields that we require and double clicking to rename any fields:  Step 2 - Filter Next we want to filter all of the rows where the Series has a null value or is preceded by a 'N'. First we can select the Null, N1, & N2 values in the profile pane and then exclude these values. We should now have the numbers 1-5 remaining.  We can also tidy the Rank field at this stage by removing any letters. We can use Tableau Prep's cleaning functionality to help us with this:  Step 3 - Points with Double Point...

Challenge by: Jenny Martin As I've mentioned before in a previous challenge, I'm a big fan of a quiz show called Richard Osman's House of Games. However, I've often found the way that they decide the overall winner of the week a little troubling. Each day the player who has scored the most, will receive 4 points, 2nd place will receive 3 points, 3rd place will receive 2 points and last place will receive one point. These points will be added up across the week to determine the overall winner, but with a twist! Each Friday double points are awarded so 1st place receives 8 points and so on. This leads me to wondering: Would there be a different winner if there was no double points Friday? What about if participants weren't ranked at the end of each day and they had a running total score across the week instead, would that lead to a different winner? What about doubling the scores on the Friday, instead of the points awarded? Input Luckily I didn't have to collect ...

Solution by Tom Prowse and you can download the workflow here .  This week we take a first look at one of Tableau Prep's newer features, Parameters! We used parameters to recreate the classic use case of a Pareto chart to see how many customers make up a certain percentage of our sales. Step 1 - Total Sales per Customer After inputting our data, the first step that we want to do this week is to calculate the total sales for each customer. As some customers have multiple rows of data, we need to use an aggregation tool to total these up. The setup for the Aggregation step is: Group by Customer ID, First Name, Last Name, then Sum Sales:  As a result we should now have a single row for each customer and their total sales: Step 2 - % of Total Sales Next we want to calculate how much each customer represents in the total sales. First, we want to calculate the total sales across all customers by using a Fixed LOD: Total Sales We can then use this to calculate the % of total for each...