Posts

Showing posts from May, 2020

2020: Week 22

Image
For many of the Preppin' Data challenges, we generate randomised data in order to keep the datasets freely shareable. Sometimes this can end up being more challenging than the challenge itself! If you've ever had to anonymise data so that you can publish a visualisation that you built using sensitive data, or built a dataset to work off of whilst waiting for access to the live data, then I'm sure you can sympathise with how frustrating this can be! You don't want the numbers to be so random that they appear unrealistic, but you also don't want to manually decide upon each value. Randomising the data at the lowest level of detail is one option, but can result in values varying too drastically when aggregating the data. A growth rate of 56% from one month to the next isn't always very realistic! This week's challenge is all about the process of generating the data for last week's challenge .   It's unlikely you'll get the exact same numbers s

2020: Week 21 - Solution

Image
You can find our full solution workflow below and download it from the Preppin' Data Community page ! For week 21 we were looking at a variety of different metrics in order to do some competitor analysis. These metrics included Market Share = Company Sales / Total Market Sales Growth = (This Month Sales - Last Month Sales) / Last Month Sales Contribution to Growth = (This Month Sales - Last Month Sales) / Overall Last Month Sales Outperformance = Company A's Growth - The Growth of the Rest of the Market excluding Company A Output 1  Step 1 - Calculate Total Sales After inputting the data source, the first step for Output 1 is to use an aggregation tool to calculate the Sum of Sales by Company and Month. The setup for the aggregation will look like this: Once we have aggregated the sales by month and company, we can then use a Rows to Columns pivot to get the monthly sales into a column of their own. The pivot setup looks like this: 

2020: Week 21

Image
Chin & Beard Suds Co are relatively new players in the Soap Market and are looking to do a bit of analysis around their competitors. To do this, they'll be looking at a variety of different metrics. Market Share = Company Sales / Total Market Sales Growth  = ( This Month Sales - Last Month Sales ) / Last Month Sales Contribution to Growth  = ( This Month Sales - Last Month Sales ) /  Overall Last Month Sales e.g. if calculating the Contribution to the Market's Growth then the numerator would use each Company's sales whilst the denominator would be the Total Market sales Outperformance  = Company A's Growth - The Growth of the Rest of the Market excluding Company A Input Requirements Input the data . At a total sales level for each company (i.e. not taking Soap Scent into consideration):  Calculate each company's Market Share for April. How many bps* has this changed from March's Market Share? *10 bps = 0.01% Calculate each co

2020: Week 20 - Solution

Image
You can find our full solution workflow below and download it from the Preppin' Data Community page ! This week we are looking at solving an encrypted message by using a couple of techniques that aren't native to Tableau Prep. These techniques include splitting a string that doesn't contain any delimiters and also concatenating a string when aggregating. Step 1 - Input Encrypted Message  The first step is to input the Encrypted Message data table, this is one row that contains our encrypted message.  To help us split the string into the correct number of characters, we first need to identify how many characters there are in total. We can use the LEN() functionality to determine this:  Length len([Encrypted Message]) We now know that there are a total of 23 characters in our message. Step 2 - Join Scaffold The next step is to input our scaffold data set, then we can join this using the newly calculated Length field. Our join cond

2020: Week 20

Image
There are a couple of techniques that I use when Preppin' my Data that aren't quite native in Tableau Prep yet. So I'm curious to set a challenge which requires them and see the different work arounds that people come up with! Splitting up a string into individual characters, sometimes referred to as tokenising. Currently you need to have a specific delimiter when splitting a field - what if I wanted to specify the length of each chunk that I want the string to be split into?   Concatenating strings when aggregating. Currently you can only count the values or return the min or the max, but sometimes I'd rather concatenate the multiple values! To play with these techniques, we're looking at ciphers for this week's challenge. You've received an encrypted message and need to decode it using the provided cipher!  Inputs There are 3 inputs this week. You may not need to use all of them, depending on how you approach the challenge. Require

2020: Week 19 - Solution

Image
You can find our full solution workflow below and download it from the Preppin' Data Community page ! This week was a follow up task from last week so that we could further analyse the Liverpool line-ups from this season's Premier League.  Our first task was to use the previous solution's workflow, along with a couple of other data sources in order to further our analysis. Step 1 - Calculate Goals Scored The first step is to calculate how many goals each team scored, so we start by creating a branch just before the final aggregation tool on the previous workflow: From here we can change the [No.] field to a Whole Number, then remove any unneeded fields for this part of analysis. These include: Comp. Date Player Name Subbed Off? Subbed On? Subbed On/Off Sub Number Mins Played  Appearances The next step will be to isolate each of the scores for each team. We can do this by splitting the score field using an automatic split on the [Result]