BYU Student Author: @Marta_Ellsworth
Reviewers: @Andrew_Wilson, @Sterling_Lane
Estimated Time to Solve: 20 Minutes
This is an intro challenge that is part of the Alteryx Learning Path.
We provide the solution to this challenge using:
- Alteryx
Need a program? Click here.
Overview
Welcome to Simple Screenprinting. You are a key member of the analytics team who is adept at navigating through data to extract meaningful insights. Lately, the sales department led by your boss Adam has been curious about various aspects of the company’s sales performance. Adam has a list of questions that need answers, and you’ve decided that the best way to tackle this task is through the powerful capabilities of Alteryx, specifically by leveraging its powerful Summarize tool. The instructions include the questions from Adam. Your deliverable is an Alteryx workflow with all these questions answered.
Instructions
- How many items of each size (XS-3XL) do we have in each color across all products?
- Use the OrderDetail table and group by Color and then sum the columns QtyXS through Qty3XL.
- For all orders we placed, when is the latest due date?
- How many unique customers are we billing?
- Count distinct the JT_BillToName from the Orders table.
- What is the average art charge? Make sure to ignore the blanks and zeros since we only want the average for when we charge for it.
- Use Average – Ignore 0’s on the Charge_Art column.
- Change the column name to Avg_Charge_Art.
- How many jobs has each customer ordered?
- Group by the CustomerID and count the JT_Number.
- How many SalesRepID’s are we missing?
- Can you write a list of all of our employees in one cell separated by commas?
- Use the Employees table and concatenate the names using the Summarize tool.
Join the Orders and Order Detail tables on JT_NUM for the last three questions.
- What is the average quantity ordered for each state?
- Group by the JT_BillToState and average the QtyOrdered.
- What’s the average unit price for each job?
- Group by JT_Num and average the UnitPrice
- Which order is the smallest order in terms of quantity of items ordered?
- Group by the JT_Num and Sum QtyOrdered.
- Use another summarize tool and find the Min of the Sum_QtyOrdered.
Data Files
Suggestions and Hints
- Go to the Alteryx Learning Path, for specific summarize tool help.
- Use the inner join and join on JT_Num when you join the two tables. This is the “J” output from the join tool.
- For question 10 make sure to use two summarize tools to get the correct answer.
Solution
