Preppin' Data

At Chin & Beard Suds Co we are developing a growing base of customers ordering on a regular basis, as a result we have decided to release a subscription option. Customers are now able to choose a package of soap(s) they wish to be sent from our range and the frequency they would like to receive their basket of soapiness. We have a total of 7 different types of products available to choose from and customers can select any number of, or variation. A current list of subscriptions made can be found in our customers table. Reference tables for product aliases and frequency aliases are available in our subscription products table and subscription packages table. One option is our mystery package…this could be any of the other 6 package types. To make our customers happy, we have decided to mimic our current customers’ choice of package type. Therefore, if more people like the Active, it should be more likely that we would send this out as the Mystery subscription type. So how much sh...

Recently here at Chin & Beard Suds Co, we've become suspicious that some our employees in our flagship store (found on the 96th floor of the Shard) are slacking off and taking their jobs for granted.  We think their productivity might be linked with how close they are to the nearest manager and who they're interacting with. To investigate this we've spent time undercover observing 3 random employees during a typical working day and recording whether they're on task. We record in intervals of between 1 & 3 mins and also note down the rough proximity of the manager on duty and who they interacted with most during each interval. We've also noted down the time we started observing each employee but not the actual start time of each interval. This current data format doesn't work well in Tableau, so we need to do some clean-up and find a way to get some actual date-times for each interval. The input. Requirements Input the data file . For each ...

You can view our full solution workflow below and download it here ! The full solution workflow. Comparing dates against Valentine's Day To start with we need to calculate whether each date is before or after February 14th 2019. For this challenge we’re counting Feb 14th itself as ‘Pre’ Valentines day doesn’t end until midnight that night. To calculate this we can simply use some Boolean operators in a calculation: [Pre / Post Valentines Day] IF [Date] > #2019-02-14#   //If the date is after Valentine’s day…     THEN "Post"             //then label it as ‘Post’…     ELSE "Pre"              //else label it as ‘Pre’.   END This returns the correct label of ‘Pre’ or ‘Post’ for each date. This works as Tableau allows you to manually provide dates in the following format: #yyyy-mm-dd# . Create a Running Total of ...

At Chin & Beard Suds Co (our Preppin' Data fake company), one of our non-executive directors has asked about the effects of Valentine's Day on our store sales. Sadly, the non-exec director isn't very good with Table Calculations in Tableau Desktop so we have promised a data set to allow them to build their own views with all of the calculations pre-computed. Here's the dashboard the non-exec director wants: Here's my version Requirements: Input the Data Determine if the sales date is pre- or post- Valentine's day Work out the running total of sales for each store, restarting after Valentine's day to allow us to compare the two 14 day periods Output: One data set (and dashboard for bonus points - remember no table calcs!) Your output date should have: 56 rows (57 including headers) 5 columns: Date Store Pre / Post Valentine's day flag Daily Sales Value Running Sales Value (remember this should restart per store and ...

You can view our full solution workflow below,  download it here , or view the walkthrough video further down! Our full workflow solution. This week we’re using straight out-the-box Tableau Prep and taking away the power of writing calculations in order to highlight some of the great features that are built into it. Some of the things used within this challenge are: Splitting a field up into multiple fields. Pivoting columns to rows. Filtering data. Cleaning up data (optional). Aggregating data. Joining data together. And all this without having to do any typing other than renaming some fields for clarity! This will be quite a simplistic write-up, but that’s just a reflection of how simple all these tasks are once you know how to do them. Splitting up the ingredients. Click on the [Ingredients] field and click ‘ Custom Split… ’ (either from the profile pane toolbar or by clicking the ‘ More Options ’ dots on the field itself & clicking ‘ Split Values ’...

Growing up in the 90s, one word instantly draws to mind an image in my head. The word is Pivot and the image is: image from: Growth Hackers So why do we need to understand about pivoting data (not sofas in stairwells) for data analysis? When do we need to Pivot in Tableau Prep? Tableau Desktop needs the data to be in structured columns. Each column can hold one type of data and can then be used on either shelves or cards in Desktop to alter the view on screen.  For example, a common need for pivot is when a new column is added for each new date in a data set. The reason why this data structure isn't great in Tableau is two-fold. One, if I want my dates to form an x-axis, they can't with this data structure as I need one data field (column) to hold all the different dates and another column to hold all the relevant values for each of those dates. Secondly, if the file is updated, Tableau won't automatically add the new column in to your analysis. If the new...

This week's challenge is to NOT USE TYPED CALCULATIONS in Prep to solve the challenge. No cheating with copying and pasting formulas either - just use Prep Builder's functionality. Last night, we went to have a burger at a local restuarant (hi Brickfields in Brockley !!) and I noticed their cocktail menu looked ripe for a data prep challenge. What I wanted to understand is the cost of the ingredient linked to the price of the cocktail? Requirements  Input the data file Name the columns 'Cocktails', 'Ingredient' and 'Price respectively  Seperate the ingredients and create a single column of them Work out the position of the ingredients within the list of ingredients in each cocktail Work out the 'average price of the cocktails that ingredient is used in' Add the original cocktail price back in Output the file Output 5 Columns 53 Rows (54 including headers) For comparison,  here's our output files .  Don't to ...

You can view our full solution workflow below and download it here ! Our full workflow solution. This week saw our first collaboration with Workout Wednesday ! For the Preppin’ Data solution, the order of steps is very interchangeable this week – as long as you join on the correct fields it should work regardless of whether you join at the very start, very end, or somewhere in the middle. There are two particular steps I’ll cover here: Obtaining all the [Fellow Artists] . Removing duplicate records. However to view a full walkthrough of the solution check out the video below. Obtaining the [Fellow Artists] This is probably the hardest part of the task. I’ll outline the steps below but you can find it demonstrated in the solution video. If the [Concert] field contains a ‘/’ then it is a list of artists. If it doesn’t include a ‘/’ then it’s a concert name like “ Divide World Tour ”. We want to both filter out these concert names as well as Ben and Ed’s names so that o...