Free Trial

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.

SUBMIT YOUR IDEA

Challenge #381: Average Monthly Sales with a Twist

AYXAcademy
Alteryx
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.

 

 

 

Challenge_381_Image.jpgThe 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.

benjimoser
9 - Comet

4 different Solutions

Spoiler
Screenshot 2023-07-26 152434.png

 

Luke_C
17 - Castor
17 - Castor
Spoiler
image.png
Kenda
16 - Nebula
16 - Nebula
Spoiler
two solutions, both using multi-row formula
 
Capture.PNG
 
Watermark
12 - Quasar
12 - Quasar

 

 

Spoiler
MT_Solution_381.jpg

ahsanaali
11 - Bolide

See solution attached.

Spoiler
Screenshot 2023-07-24 174423.png
mmontgomery
11 - Bolide
11 - Bolide

Probably more steps than needed but matches answer

Spoiler
Still can't upload images
braveraj
11 - Bolide
Spoiler
challenge_381_workflow.pngchallenge_381_results.png
ed_hayter
12 - Quasar
Spoiler
Solution. Ended up doing it on desktop as I had a mental block on cloud without multi-row formula

Challenge 381_Workflow_Screenshot.png
Tgigs
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