Challenge #3: Running Averages
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Ok, I little bit of brute force but I got there:
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
My solution.
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Also a similar solution to many above
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.