Posts

2020: Week 23

Image
This week's challenge is related to quiz results and answers! Many people are taking part in virtual pub quizzes with their friends and families and there is always the made panic at the end to total your scores! Therefore, for this week's challenge we are going to try and automate this process a little by solving this in Tableau Prep!
Each pair of participants have created their own set of 10 multiple choice questions. Therefore, creating 5 rounds and a total of 50 points up for grabs.  Input1. Participant Answers

This contains:  Name - Name of the participantsRound 1-5 - These were the rounds of the quizEach cell shows the answers for that round. The first letter represents the answer to question 1, the second letter is question 2, and so on. 2. Correct Answers

This contains the correct answers for each of the rounds. The answers are in the same format as the previous input. RequirementsInput DataIdentify the participants and correct answer for each questionCombine the two input t…

2020: Week 22 - Solution

Image
You can find our full solution workflow below and download it from the Preppin' Data Community page!

This week we were looking at the process of creating realistic dummy data that was used for last week's challenge. 
Step 1 - Calculate Market Share The first step is to input the Companies data set, this provides us a list of each of our companies. From here we want to add an ID to each of these companies. To do this we can use the Ranking functionality to rank our companies in a descending order: 
ID
We now have an ID for each of our companies that looks like this: 

From here we can then input our 'Random' data set that contains an ID field and a Random number. We then use the ID field to join this onto our Companies table:

After the join we are left with 3 fields; Company, ID and Random.


As we need all of our random figures to be greater than 0, we can use the ABS() function to do this: 
Random ABS([Random])
We now have all our randomised numbers as positives.
The next …

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 since we&#…

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 SalesGrowth = (This Month Sales - Last Month Sales) / Last Month SalesContribution to Growth = (This Month Sales - Last Month Sales) / Overall Last Month SalesOutperformance = 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: 

We now have a row for each of our companies, wit…

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
RequirementsInput 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 company's Growth in April. Make sure everything …