2021: Week 31 Excelling in Prep
Challenge by: Carl Allchin
When you are working with data in most organisations, you will frequently come across requests from Excel users using Excel terms. This week's challenge looks at term that is ubiquitous with Excel - a pivot table.
Pivot tables are often contain summarised data values, have totals and filter out certain parts of the data set. The challenge this week will be to take an input and create a pivot table. Pivot tables are likely to be structured differently to most of our analytical outputs.
Input
The weekly sales of Bike Components from Preppin's bike store Allchains is what we are analysing. The returns are where the product has been deemed faulty before it's sold.
Requirements
- Input data
- Remove the 'Return to Manufacturer' records
- Create a total for each Store of all the items sold (help)
- Aggregate the data to Store sales by Item
- Output the data
Output
6 columns:
- Items sold per store
- Wheels
- Tyres
- Saddles
- Brakes
- Store
4 rows of data (5 rows including header)
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
