community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!

Challenge #3: Running Averages

Alteryx Alumni (Retired)

We hope you enjoyed last week's challenge. For the third challenge let’s look at creating 3 and 6 month running averages.

 

The goal is to create 3 and 6 month running averages for the values contained in columns: u.CAGI, d.CAGI, u.IR, d.IR, u.NonIR, d.NonIR. Create the averages by horsepower (HP) Category. 

 

We have classified this challenge as intermediate. We would love to hear your feedback.

 

Have fun!

 

UPDATE 2/6/2016:

The Solution has been updated to include impute logic

Meteoroid

Hi 

 

is this output set correct?

 

for example row 73 - 6 months averages looks corrupted (not based on HP category)

 

btw excelent exercise! I really want to see original solution next week.

 

Thank you

 

Martin

 

 

Atom

Thanks for your week's challenge, but there are some points I have to criticize in your solution:

 

1. There is a misstake for example on row 139-140/r3mo_d_CAGI there is set a row of "Null" which should be valid data.
The mainproblem is that "Null + x" always returns "Null" as result.
So Null fields have to be replaced with 0 and then reset back to "Null".

 

This problem occurs in the whole datasheet.

 

2. In the solution there are a lot of rows set to 0 which should be "Null" for example row 213-216.

 

I got a solution which hopefully covers those bugs, but it is not a sizeable solution and very much code has to be writen.
Im gonna attach the solution, if you find any misstakes or got a easyer way to solve it, please tell me.

I hope for a sizeable solution, from alteryx, because without I doubt to use it in terms of bussines.

 

 

 

 

 

Alteryx Certified Partner

Ok, I little bit of brute force but I got there:

Spoiler
First, I generated a RecordID field then transposed the data by the key fields to give a single column with the value data:

Transpose to group.PNG

I then created the calculations using the multi-row formula tool for the 3 and 6 month averages:

Create calculations.PNG

three month expression.PNG

The expressions were the same just scaled up for 6 months.

Following this, I crosstabbed the data back to return it to the original layout. I did this for each of the 3 and 6 month value calculations. 

Crosstab to regroup.PNG
I then used the Join Multiple tool to join these two streams and the original data stream from the beginning of the workflow, and then renamed accordingly:

Join to main body.PNG
Nebula
Nebula

Hi all,

 

A lot of similarities in my solution compared to the provided solution - main differences are:

- Using the "Average" function with closest value gives the wrong answer for the second month of every product for the 3month; and for rows 2,3,4,5 for the 6 month

     - Only accurate way to do this is to do a multi-row formula to do a row-count; and a multi-row formula to do a sum; and then a formula on each row to work out the average (sum/count)

- I used a formula to rename fields rather than dynamic rename - but I prefer the dynamic rename

 

Example of the difference:

Average function using multi-row with "Closest value" set to deal with missing rows

Row 1: 1          - 3 month average is 1

Row 2: 2          - correct 3 month average is 1.5 but the "closest value" method gives (1+2+2)/3 instead of (1+2)/2

 

Alteryx Certified Partner
Alteryx Certified Partner

Looks like the sample output has at least 1 truncated column name.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Magnetar
Magnetar

My solution. 

 

Spoiler
Feels like a lot of cross-tabbing... but it worked, and it felt fairly organized/methodical, so I left it.
WeeklyChallenge3.JPG
Alteryx Certified Partner

Challenge #3 complete!

 

 

Spoiler
challenge3.jpg

 

Alteryx Certified Partner

My solution is also similar to others

 

Pulsar

Also a similar solution to many above

Spoiler
Spoiler image.png