BYU Student Author: @DylanKing
Reviewers: @Boston, @TylerBooth
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
You are the new owner of a used car lot. The previous management was buying models from any and every manufacturer you can think of: Audi, BMW, Saturn, Volvo, etc. As the new owner, you hope to shift your focus to acquiring used cars from manufacturers whose models have the least amount of average depreciation on their car models. You imagine that doing so will prevent you from taking big losses by holding onto cars for long periods of time.
The data provided gives you access to information on manufacturers, models, sales in thousands of units, the resale price, the price in thousands of dollars, and more from 2008-2012. With the data provided, use Alteryx to find the top 10 manufacturers with the lowest average depreciation across their various models. Follow the instructions below.
- Calculate Depreciation for each specific model. Convert the number from thousands to regular units (i.e. 5.14 in thousands to 5140)
- Remove any rows of data that have a null price or resale value.
- Average the depreciation for each model across the manufacturers. Round to two decimal places.
- Remove any rows of data for manufacturers who did not have at least 3 models to prevent outliers.
- Sort by average depreciation in ascending order. The output should only include the manufacturers and their average depreciation. Limit the output to the ten manufacturers with the least average depreciation
Suggestions and Hints
Depreciation = Price - Resale Value
That one threw me for a bit. I used the wrong field to start, the “Sales_in_thousands_of_units” and not the “Price_in_thousands” field. I should have read the data dictionary more closely!
Also, it was easier once I realized that each car model was only listed once, it wasn’t transaction data. After that, much easier. My solution is below.
Great Alteryx challenge! I forgot to change the depreciation that we calculated from a string to a number, so I spun my wheels for a while trying to get the average. I also didn’t know about the fixed decimal data type, but that comes in handy for this one. Overall, solid for brushing up on formulas, summarizing, filtering, and some of the other basic Alteryx tools. This was my solution:
Loved the flow of this one Dylan. Thanks for posting.
I made it more complicated than it needed to be, but I ended up simplifying it a bunch for my final solution. Here’s what I got!
Alteryx is so forgiving. I used Sales instead of Price for my initial depreciation formula, but once I fixed that, everything else down the chain flowed so smoothly. I also appreciate the ability to pull other data flows off of different points; I worried that my summarize functions would be off, so I split off the count from the average to double-check my work. Thanks for the challenge, Dylan!
This was a good review of the filter and select functions. Overall an enjoyable challenge. Thank you!
Cool problem! I probably didn’t take the most efficient approach in regards to filtering out manufacturers without at least 3 models but it worked out anyways.
Same. I ended up going a bit overboard with what I did. Also, I need to figure out how to rewrite the descriptions below each tool like how you have it presented. I feel like that makes it much more understandable and aesthetically pleasing.
Hot take?..Data wizzes aren’t your average nerd…We’re super creative ones that like to make the data look pretty! haha
This was a great challenge! I enjoyed learning about the ability to round with a fixed decimal. Thanks!
much easier than I thought it would be, but still great application to better my alteryx skills! thanks for the Challenge!
This was good practice!
TechHub Nov3.yxmd (40.1 KB)
I enjoyed getting to practice with a fixed decimal. Thanks for the challenge!
This one challenged me a little bit when I decided to figure out how to round without using a fixed decimal. It was good practice
This was a cool challenge, I tried a couple of different ways to count the number of models for each manufacturer, but couldn’t figure it out. The solution showed me a cool way of doing it thinking outside the box. I didn’t even know the CountDistinct was an option for the summarize tool until now
TechHub2.yxmd (20.8 KB)
Loved this challenge. I am constantly reminded that tasks are never as hard as they look. You just have to get into it.
Here’s my solution! Even though I used the fixed decimal type it didn’t cut off the decimal place to two.
This one was a little tricky with the double filter. This problem was good at reminding me to take a step back and think about what data I am trying to find. Alteryx has shown me how to break things into simple steps.