Preppin' Data

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

Challenge by Amalia García-Vellido Santías We have another guest challenge creator with this week's challenge coming from Amalia.  This week we want to analyse the orders that customers have made over a period of time in our restaurant Serendipia. In order to identify how much money we earn each day of the week and also to discover who our top customer is. We are going to be using calculations, pivots and aggregations so lots of the fundamental techniques that are used within data prep! Inputs Menu - contains the menu of the restaurant (notice that the structure is not ideal) 9 fields 10 rows (11 + header) Orders  - each row represents the order a single customer have made at a certain date 3 fields 40 rows (41 + headers) Requirements Input the data Modify the structure of the Menu table so we can have one column for the Type (pizza, pasta, house plate), the name of the plate, ID, and Price ( hint ) Modify the structure of the Orders table to have each item ID in a different r...

This week we have a special guest setting the challenge: our colleague at The Information Lab,  Gwilym Lockwood ! Here at Chin & Beard Suds Co., we’ve been looking at our sales data. The numbers are good, but we’re noticing that people tend to buy the same stuff each time, which means there’s a lot of untapped opportunity for cross-selling. For example, it’s nice that Gwilym buys shaving soap once every two months, but he doesn’t buy anything else. What other products might he be interested in? How can we give him targeted recommendations that he might actually like? What do other customers with his purchasing habits also buy? We can use market basket analysis to answer these questions. In market basket analysis, we need to calculate three key metrics: support, confidence, and lift. We calculate these metrics for individual items and for association rules between items. You can read a lot more about market basket analysis here . I’ll go through a quick example of an as...

You can view our solution workflow below and download it here . Full solution workflow. There are 3 main learning objectives for this week's Preppin' Data challenge: Using "Wildcard Unions" & file paths. Using aggregations to calculate "percent of total" fields. Creating Filter calculations. Unioning the data and getting the regions We’ve got five CSVs, one for each region, and all of them have the same data structure – sounds like a call for a Wildcard Union input! Instead of inputting each file separately we can: Applying a Wildcard Union Add one of regional stock files to the canvas. Switch to the Multiple Files tab. Select Wildcard Union . If they share a folder with other files, define a “ Matching Pattern ” of “* Stock Purchases.csv”.  This means only  files that end in “Stock Purchases.csv” will be imported into the union. Click Apply . From here, Tableau Prep automatically unions all the data together for yo...