Since we got introduced to Alteryx today, I thought I might as well try out another one of these weekly happenings in the Data Analyst community. This one is called the Alteryx Weekly Challenge. The idea is simple. You are presented with a data source and a desired output format. To get from one state to the other, some serious planning and reshaping of the data is needed. Think of it like a puzzle. You know what the final result should look like and now you're using the tools available in Alteryx and you're puzzling together the pieces to get to the expected result. And chances are that there are multiple ways to reach that goal.
So this week's weekly challenge is called "Average Monthly Sales with a Twist", and as the name suggests, it requires a little more than just vanilla averaging some monthly metric. The provided data consists of numbers of sales per employee, split up in monthly columns as shown in the screenshot below:
And using this data you're now supposed "to determine the average sales for each employee, considering ONLY the first 3 consecutive months beginning from the month they made their first sale of the year". The shown table of input data already suggests that some pivoting might be involved. In the end, the screenshot below shows the Alteryx workflow that I came up with, and I'm 100% certain that it's not the best performing or shortest possible soultion. But in the end, this is my first alteryx challenge, so I'm happy I've been able to solve it within a reasonable amount of time:
And here are some of the tools I used to solve the challenge:
- Transpose Tool for pivoting & grouping by employees
- Select Tool to do type conversions & drop columns (fields)
- Filter Tool to preprocess data for sampling, like removing all 0 sales months
- Sample Tool used for selecting the first row per employee
- Join Tool for merging back the sampled data to data from the previous state
- Multi-Row Formula for calculating the average across different rows
- Sorting Tool to sort employees to match the sorting in the target output
So after my first day of using Alteryx I can already tell the importance of knowing the common tools well and have an understanding of their use cases. Also, I do like the puzzle-like challenges, so I'm sure this will be an ongoing thing for me.