Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

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 #3: Running Averages

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

 

 

martinbosak
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

 

 

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

mceleavey
17 - Castor
17 - Castor

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.



Bulien

SeanAdams
17 - Castor
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.

MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
NicoleJohnson
ACE Emeritus
ACE Emeritus

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.

Laurap1228
11 - Bolide

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.

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

estherb47
15 - Aurora
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.