Preppin' Data

 Created by: Carl Allchin The CEO was delighted to be able to analyse their individual Spin Classes last week. Now they would like to understand their overall performance. Their cycling equipment doesn't capture speed so you will need to guess their average pace.  Input One csv file (same as last week) Requirements Input data Merge km's and min's as Minutes Split the unnamed column into Coach Calories Music Type Convert the Dates to Years Create a parameter to let the user select any speed as the average riding speed (KPH) Your values may differ depending on my average speed (I used 30 kph) Create the following aggregations Total Minutes Total Minutes per Coach (find the most minutes per Coach) Calories per Minute per Coach (find the max calories per minute per Coach) Avg. Calories per Ride Total Rides Total Distance ((Mins/60)*Speed Parameter) Avg. Calories per Minute  Combine all the answers and restructure your data if necessary Output the data Output Using 30KPH as t...

Video Solution -  Solution by Tom Prowse and you can download the workflow here . This week we have a deeper dive into using Parameters in Tableau Prep and how we can use them to make different selections when filtering our data. We are looking at the different rides from some Spin Classes where we want to know the top rides for different coaches and music types. Step 1 - Clean Metrics The first step is to clear up the table so that we have the correct metrics. First we need to rename Value to Mins then we can remove the Units and Type fields. This will give us the duration of each ride.  Next, we need to split apart the F5 field to get the information about the Coach, Calories, and Music Type. We can do this by splitting all values on '-': After the split we can rename the fields so our table should look like this:  The last task we need to do in this step is to make sure the Music Type is in Title Case (we covered this a couple of weeks ago in a challenge!). The calcula...

 Created by: Carl Allchin The CEO of Chin & Beard Suds Co has a challenge for you... but it's not exactly work related!  They want you to create an application that will allow them to analyse their Spin Classes. They've collected all of their data but just want to know their top rides for different coaches and music types. As CEO, they also expect to be able to pick the number of entries returned too!  Input One csv file Requirements Input data Merge Km's and min's as Minutes Change 'Value' to Mins Split up the unnamed column into: Coach Calories Music Type Change Music Type values to be Title Case (first letter of each word is capitalised) Create three parameters: Music Type Coach Top N  Create a way to return the Top N value selected and order the file with the highest calories burnt at the top Create filters so only the parameter selection remains in the output data set For Top N parameter it's all the values up to that number Output the data but use ...

Solution by Tom Prowse and you can download the workflow here . The challenge this week is to try and identify how long there is between each of the orders for each product and location. There were some different answers across social media with different tools and different solutions... but this is how we solved it! Step 1 - Input Tables The first step is to input all of our data sources and combine them so we have a single table. First we can focus on the two orders tables and combine these using a Union . Once we union the two tables together, we need to ensure that the mismatched date fields (Sales Timestamp & Sales Date) are merged together into the same field. We can do this by selecting Sales Timestamp and then using the + icon on the Sales Date row to merge these together. After merging the fields, we then want to replace any null values with Online. This will be in the Store field and nulls are created as this field isn't present within the Online orders table. All we ...

 Challenge by: Carl Allchin This is the midpoint of our month of Intermediate challenges building on from the challenges in beginners' month.  This week's challenge will again test your problem solving skills but I expect lots of different ways to achieve the same output (especially in different softwares). Remember to plan your path through the challenge to help you keep on task and not going off on tangents.  We have collected the sales data for two stores and our online shop from the start of the year for a few weeks. We'd like to know how long between the orders of each of the types of products in the different locations.  Input There are 3 inputs this week: Input 1 - Product Lookup Input 2 - Instore Sales Input 3 - Online Sales Requirements Input the data sets Link the Instore and Online sales together to be one data source Call the Nulls in the Stores field Online  Link in the product Lookup to name the products instead of having their ID number Create th...

Solution by Tom Prowse and you can download the workflow here . This week we looked at a classic problem for anyone who is lucky enough to own property... how much is left on a property mortgage? We want to do this for each of our stores so lets look at how we can solve the challenge. Step 1 - Months to Repay The first step is to calculate how many months we have remaining to repay the mortgage in full. This consists of a few calculated fields as we need a start date, how much is being paid, and then to calculate the number of months until the end date. Today MAKEDATE(2022,8,10) This creates a 'today' field based on 10th August 2022. In a real life situation we could just use the TODAY() function as this will update based on the actual date. Monthly Capital Repayment [Monthly Payment]*([% of Monthly Repayment going to Capital]/100) This calculates how much capital is being paid off each month. Months to Repay CEILING([Capital Repayment Remaining]/[Monthly Capital Repayment]) Th...

 Created by: Carl Allchin Our month of intermediate level challenges continues with a classic problem for those of us who are lucky enough to own property... how much is left on a property mortgage? Chin & Beard Suds Co. (C&BSCo) are looking at how much they will need to pay each month for the combined amount on their stores. They know a few details but not how much they are going to have to pay and for how long. Can you help? Input  One file of data : Requirements Input the data Create a field for today (10th August 2022) Create a data field to show how much capital is paid off each month Create a data field to show how many months are needed to pay off the entire debt (whole months only) Create a field when the mortgages will be paid off by (Assuming a payment is to be made in August 2022) Create a row per month between now and when the mortgage is paid off showing: How much is still to be paid off for that mortgage? Call this field 'Remaining Capital to Repay' How m...

Solution by Tom Prowse and you can download the workflow here . This is the first of our intermediate challenges throughout August! We aim to introduce some more complex techniques that you can use to clean and prepare your data. For this challenge we want to use a parameter to make a store selection whenever we run the workflow, this allows us to easily segment our data into separate outputs. Step 1 - Liquid Products First we need to prepare our data so that we identify the product type for each row. This is in the Product Name field, along with the quantity, therefore we can use a split to bring this into separate columns. We can then rename the two new fields to Size and Product Type. Then from the Product Type we want to Keep Only the Liquid rows. At this stage our table should look like this: Step 2 - Top 10 Sales Now we have a cleaned table, we can start to aggregate and then find the top 10 sales for each Product Size, Scent and Store combination. First we need to aggregate to c...