Challenge #3: Running Averages

[GeneR]

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]

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]

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]

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.

[SeanAdams]

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]

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.

[NicoleJohnson]

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]

Challenge #3 complete!

Spoiler

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

[Natasha]

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]

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.