2019: Week 37

Aggregating Data Sources to a good level for analysis is always a "fun" decision to take. Leave the data at too detailed level of granularity and you may face slow performance but have high flexibility in what analysis you can do. If you aggregate the data to a too higher level, you might get great performance from the software you are using but suffer with the flexibility of questions you can answer.

Well, what if we just want to form a table of those answers? This week's challenge is looking to do exactly that.

At Chin & Beard Suds Co. we have had a higher level than normal of returns. We want to take some of our sales data from the first half of the year to understand what might have gone wrong so we can make sure we don't make the same mistakes in the future. To start the analysis, the company's leadership team have asked for '% returns' and then to compare different % returns for different factors in the business:

• The full data set
• Month of trading
• Product Type
• Fruit / Non-Fruit Scents

We even have created a visualisation that uses Parameter actions if you want a bit of a desktop challenge too.

Requirements

• Input Data (all 6 files)
• Form a Date using the file number as a month
• Categorise Product Types as Fruit or Non-Fruit
• Form your % Return Rates for:
• The full data set
• Month of Trading
• Product Type
• Fruit / Non-Fruit Scents
• Create one output table
• Optional - Build the visualisation too allowing the user to click on a 'Type' to set that as the benchmark rate of returns that all other types will be compared against

Output

• One file:
• 4 Columns:
• Type
• Returned Orders
• Total Orders
• % Returned
• 13 Rows (14 including Headers)
• Optional...and the dashboard too if you want?

For comparison, here's our output file. Don't to forget to fill in our participation tracker!