Challenge #381: Average Monthly Sales with a Twist

[AYXAcademy]

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.

[benjimoser]

4 different Solutions

Spoiler

[Luke_C]

Spoiler

[Kenda]

Spoiler

two solutions, both using multi-row formula

[Watermark]

Spoiler

 

[ahsanaali]

See solution attached.

Spoiler

[mmontgomery]

Probably more steps than needed but matches answer

Spoiler

Still can't upload images

[braveraj]

Spoiler

[ed_hayter]

Spoiler

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

[Tgigs]

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