We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.
alteryx Community

# Weekly Challenge

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

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

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

## 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: c.LK98, p.LK98, c.1K, p.1K, c.NLP3, and p.NLP3. Create the averages by RM Category.

Update: As of 9/20/19, the start and solution files were updated.  Your solutions may not look like those posted by Community members prior to this date.

6 - 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

5 - 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.

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

14 - Magnetar

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

Spoiler

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

17 - Castor

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

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

19 - Altair

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

Cheers,

Mark

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

Alteryx ACE & Top Community Contributor

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

My solution.

Spoiler

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

9 - Comet

Challenge #3 complete!

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

9 - Comet

My solution is also similar to others

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.

15 - Aurora

Also a similar solution to many above

Spoiler

This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.