Challenge #381: Average Monthly Sales with a Twist

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.

Challenge 381_start file.yxzp

Employee sales data.csv

flow_Challenge381.zip

challenge_381_solution.yxzp

Intermediate

Core

Transform

Accepted answers

All comments

benjimoser

4 different Solutions

Spoiler

Challenge 381_Benji.yxzp

Luke_C

Spoiler

Challenge 381_start file.yxmd

Kenda

Spoiler

two solutions, both using multi-row formula

Challenge 381_ks.yxzp

Watermark

Spoiler

Challenge 381_MT_Solution.yxmd

ahsanaali

See solution attached.

Spoiler

Challenge 381_Solution_AAA.yxzp

mmontgomery

Probably more steps than needed but matches answer

Spoiler

Still can't upload images

c_381.yxzp

braveraj

Spoiler

Challenge 381_avg_sales.yxmd

ed_hayter

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

Challenge 381_start file.yxzp

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

Tgigs - Challenge 381 Solution.yxzp

acwysor

My Solution

Spoiler

challenge_381_Complete_AW.yxmd

RolandSchubert

Spoiler

Challenge 381_solution_rsc.yxmd

Towers

enjoyed this one

Challenge 381_towers.yxmd

patrick_digan

Spoiler

Challenge 381_start file.yxmd

MadyeIBT

What a great way to start the week!

Spoiler

Alteryx Challenge 381.yxmd

Pilsner

My Solution:

Spoiler

381 (First 3 Months Average).yxmd

Gmajid786

A bit more of a unique solution shall we say. I had not encountered the multi row formula tool before so I wanted to do it with the tools I knew. first step was to identify the first non zero value month and use that as a baseline to match against the whole data set. I then converted into date time format as my identifier on where to begin the average calculation. Found out later i could have used the find and replace tool as well to assign an identifier against each month and do it that way as well!

Challenge 381_solution_Gufran.yxmd

quinnmb

My solution

quinnmb_Challenge 381_start file.yxzp

nSpire

Spoiler

Challenge 381_start file.yxzp

acarter881

Here's my solution. I don't think I'll ever understand how these Weekly Challenge sample solutions are prepared. It boggles my mind how the data type for sales is Double when it's clearly represented as FixedDecimal. My solution is more verbose than it needs to be, but it's because I wanted to match the sample solution.

Spoiler

Challenge 381_ac.yxzp

RobbieA

My solution to 381:

Challenge 381_start file.yxmd

FrederikE

Spoiler

C381.yxzp

kaiserj

😀

381_solved.yxmd

NMangera

takeaway

1. there are multiple ways to skin a cat (I would never). Cause you know. Animal cruelty

Challenge 381_NM.yxmd

annbriceAB

A fun one!

ABChallenge 381_start file.yxmd

Otto_Richardson

Spoiler

Alteryx Chalenge 381 Sales With a Twist.yxzp

Nazley

Please correct me if I am wrong but I dont see the actual solution attached I see everyone giving their answer?

Milanz

My solution ;-)

Spoiler

Challenge 381_ML.yxmd

alexnajm

Initially made it more complicated than necessary, but simplified it down a bit 😊

Spoiler

Challenge 381_start file_Alex Abi-Najm.yxzp

rachel_lynch

Spoiler

Average Monthly Sales with a Twist.yxzp

drslatier

Spoiler

Challenge 381_ds_Completed.yxmd

ggruccio

Sort Order is important!

Spoiler

I probably have a few extra tools because my workflow kept sorting and going out of the order that I wanted. Plus needed a way to get the monthly sort back in place.

Challenge 381_gg_finish.yxzp

Sebastian_Chaieb2

Hi,

I had same issue with uploading screenshot (directly from snipping tool via copy) but if I save it first in the folder as *.jpg and then drag it here it is working.

Lets try it

hnietfeld

Spoiler

final challenge.yxzp