Preppin' Data

Challenge by: Jenny Martin This week we're looking at a slightly odd way that Chin & Beard Suds Co have been structuring their store sales and target data:   As you can see, for each Store, there are 3 rows, the first being the Sales values, the second row containing the Target values for each month and the third row containing the difference between these values. It's your job to transform this monstrosity  unique table into a more conventional output that we could use in Tableau Desktop.  Inputs You have 2 options this week: Start the challenge with a Row ID already present (as pictured above) Use this as an opportunity to play with the Script step and create your own Row ID! Our solution will cover the RServe option, but you're welcome to use TabPy instead. For help getting set up with RServe, check out this blog For TabPy, check out this blog by our colleague Brian Scally   Requirements Input the data Make sure Store names have filled down correctly and remo...

 This solution is by Tom Prowse and you can download our full solution here .  This week's challenge built upon the work that we did in Week 33 and used that solution as our starting point. For this week's task we will be looking into profitability analysis and suggestions on how we can improve this for the Chin & Beard Suds Co execs.  Step 1 - Calculate Avg Units Sold The input for this week's challenge is our solution to last week, therefore we can start by opening up that workflow.  From here, our first task is to calculate the Avg Units Sold for each Scent. To do this we can use an aggregation tool, to group by Scent and then Avg the Units Sold field:  We can then tidy this field up a bit by rounding up to the nearest whole number, rounding to the nearest 10, and multiplying by 7. We do this by using this calculation:  Units Ordered ROUND(CEILING([Avg Units Sold]),-1)*7 Step 2 - Calculate Waste Now we have created the units ordered field, we n...

Challenge by: Jenny Martin This week is a follow on from last week's challenge . Chin & Beard Suds Co execs were very interested in our profitability analysis and want to see if we can make any suggestions for improvement. The first place to start is challenging the assumption that each Scent sells 100 bars a day on average. Once we calculate the actual average sales of each Scent per week, we'll see if ordering based on this figure improves profitability for C&BS Co.  Input All you'll need for this week's challenge is the solution workflow from last week, which can be downloaded here . Requirements Calculate the Average Units Sold each day for each Scent Round this upwards to the nearest whole number Round this to the nearest 10 Multiply this by 7 Use the Average Units Sold as the new Units Ordered value and calculate the Waste (=Units Ordered - Units Sold) in this new scenario For negative values, this means we need to adjust our weekly sales as we would have ...

  This solution is by Tom Prowse and you can download our full solution here .  After all the discussions around last week's challenge, this week we took our learning's and applied this to a real-life scenario. This week we want to calculate how many products we are wasting, and how much that's costing us as a result! Step 1 - Calculate Units Sold The first step is to join the Daily Sales and Scent tables together. We can do this with an inner join on Scent Code:  We can do calculate the Units Sold, as we have both Daily Sales and Price within the same table. Units Sold [Daily Sales]/[Price] Step 2 - Calculate Weekly Sales & Units Sold Now we have our units sold, we can use the Orders table to calculate the weekly groupings.  First we need to join the Orders table, we can do this with an inner join on Date:  We then want to remove the Date-1 field, as this will make it easier in the next few steps! After removing the field, we want to do a self-join with ou...

Challenge by: Jenny Martin Since we had so many interesting approaches to Carl's fill down challenge last week, I thought we'd continue the theme of multi-row data prep problems!  This week we're looking at Chin & Beard Suds Co's slightly odd inventory ordering process. Every Wednesday, they restock each scent with 700 new bars. This is working on the assumption that the average sales for each scent are 100 bars a day. Any unsold bars on the Tuesday evening are deemed "not fresh enough" and thrown away.  We've been tasked with finding out how many bars are being wasted due to this process and how much that's costing the company! Inputs We have 3 inputs this week: Daily Sales Input Orders Input Scent Input Requirements Input the data * *Edit 20/08 - Final 3 days in July Removed from Daily Sales Calculate the Units Sold (=Daily Sales/Price) For each week (Wednesday-Tuesday), calculate the Weekly Units Sold and Weekly Sales.  Hint: It may be useful t...

  This solution is from Tom Prowse and you can download our full solution here.  This week we have seen lots of different ways of completing this challenge, so I thought it would be good to discuss a couple different options! We love seeing the different solutions so please keep sharing with on Twitter or on the Tableau Forums. Solution 1 - LODs The first step is to create a self join with the original data source, which will increase the amount of data but allow us to match up the manager names with the row IDs. Before, completing the self-join, on one of the branches we want to remove all null values from the store manager field, so our data will look like this:  Once we have removed the null values, we are ready to join back to the original data source by using the following join condition:  Notice how our number of rows has now increased due to the various different matches occurring  as a result of the join.  We now have two Store Manager fields, one w...

Challenge by: Carl Allchin  Difficulty Level: We might get some [angry / frustrated] tweets about this One of the features that many users have requested is to solve a common challenge in Excel, to copy down data from the cell above if it is currently null.  As a human, we look at this data table and assume that Jenny manages Chelmsford, Ipswich and the Norwich stores of Chin & Beard Suds Co. We assume Tom manages the next four stores. Jonathan manages Northampton and Leicester.  However, many data tools don't make this same assumption and it is difficult to apply this assumption - especially when correct.  Input The table above is the input. Get it here . Requirements This challenge is about how do you copy down the name of the Store Manager into the null cell below.  Leave no nulls in the table I'm not going to give many clues as I'm looking forward to seeing what you do to resolve this.  Rules No manual entry of data points though!!  We want sol...