2020: Week 12
Would you believe that Chin & Beard Suds Co have encountered yet more messy data? It seems someone was trying to be helpful by creating an aggregated view of sales per week for each scent of soap. However, in doing so we've lost the lower level of detail of the product sizes that make up these sales for each scent. We really need this for other analysis we've been carrying out!
Fortunately, we know what percentage of sales each product size makes up for each product in each week. Unfortunately, the data isn't stored in a way that will make it easy to join all the necessary information together.
Fortunately, we know what percentage of sales each product size makes up for each product in each week. Unfortunately, the data isn't stored in a way that will make it easy to join all the necessary information together.
Inputs
There is 1 input file with 3 sheets:
Total Sales by Week for each Scent
Percentage of Sales for different product sizes per product per week.
A lookup table for linking Scent, Product ID and Size
Requirements
- Input data
- Our final output requires the Date to be in in the Year Week Number format.
- (Check out 2020.1.4 for an easier way to clean dates)
- We don't care about any product sizes that make up 0% of sales.
- In the Lookup Table, it seems the Product ID and Size have been erroneously concatenated. These need to be separated.
- You'll need to do some cleaning of the Scent fields to join together the Total Sales and the Lookup Table.
- Calculate the sales per week for each scent and product size.
- Output the data
Output
One file:
- 5 Data Fields:
- Year Week Number
- Scent
- Product Type
- Size
- Sales
- 307 rows (308 including headers)
