alteryx Community

# Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
###### IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

## Challenge #381: Average Monthly Sales with a Twist

Alteryx

A solution to last week’s challenge can be found here.

This challenge comes to us from @Gurpartap0710 . Thank you for your contribution, Gurpartap!

Use Designer Desktop or Designer Cloud, Trifacta Classic to solve this week's challenge.

The Data Analytics team has been provided with a CSV file that contains monthly sales records for each employee throughout the year. The objective is 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.

For example, if an employee did not make any sales in January and February but made some sales in March, the calculation would start from March. Therefore, the average would be computed based on the sales recorded in March, April, and May, even if there were no sales in April or May.

9 - Comet

4 different Solutions

Spoiler

17 - Castor
Spoiler
16 - Nebula
Spoiler
two solutions, both using multi-row formula

12 - Quasar

Spoiler

11 - Bolide

See solution attached.

Spoiler
10 - Fireball

Probably more steps than needed but matches answer

Spoiler
10 - Fireball
Spoiler
12 - Quasar
Spoiler
Solution. Ended up doing it on desktop as I had a mental block on cloud without multi-row formula

8 - Asteroid

My solution attached.

Steps taken:

1) Converted sales columns to numeric as they come in as text from csv file

2) Transpose dataset so columns become employee ID | employee name | month | sales

3) Convert month to date using datetime tool and incoming date format as %B

4) Filter dataset where sales are >0 and then summarize to get the min date for each employee

5) Join min date into dataset and identify the dates that match which are the first date of sale

6) Create offset field with datetimediff function to get difference in months between date and min date

7) Filter on offset 0, 1, and 2 which are the first 3 months of sales for the employee

8) Summarize the dataset by employee and average sales for the employee