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.
6/17/21: We have completed maintenance for the Search functionality on the Community. If you are seeing any issues, please try to clear your cache first. If the issue persists please email Community@alteryx.com

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

KOBoyle
11 - Bolide

Solution attached.  The solution provided seems to assume that the source data will be in chronological order within each HP Category.  I did the sorting within the workflow immediately prior to calculating the averages.

 

Ken

KOBoyle
11 - Bolide

Sean,

I'm not a statistician, but I don't think calculating a moving average with too few data points is correct either.  The "closest value" approach and your approach could both yield significantly skewed results.  Using a stock chart as a familiar use case, the 50-day and 200-day moving average charts do not begin until there are a sufficient number of data points to calculate the average.  With your solution, in Month 2 the 3-month and 6-month averages are the same calculation because only two data points are available. This does not seem right.  Within Alteryx, I think the correct approach is to select 'NULL' for the 'Values for Rows that don't Exist' setting.  This matches the behavior seen in the stock chart example.

 

Ken

A_Twa
8 - Asteroid

This was a real stretch for me - I did finally get to an answer.  However, as others have noted, there seems to be an issue with the provided solution's method for calculating the rolling 6-month avg


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

ajmnoble
5 - Atom

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

Philip
12 - Quasar

My solution for Challenge 3.

Spoiler
Weekly Challenge 003.png
sagarb
8 - Asteroid

This is a good challenge. I learnt a new tool and I got to use Transpose and Cross Tab, as I mess up with those 2 tools more than others. I took a bit of help but was able to complete the solution.


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

JORGE4900
8 - Asteroid

As some of you already pointed out, the result set does not necessarily match those on the problem. The root cause seems to be that the result set seems to have been compiled without grouping the HP Category on the 6 month multi-row function.

 

Thus, I had difficulty matching the results.


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

ADerbak
10 - Fireball

I went through multiple iterations of Multi-Row Tools using IfElse statements with modifying each field for 3mo or 6mo. While this was the right idea, it was lengthy and inefficient. I took a look at what others were doing, and, unfortunately, got so stuck that I referenced the solution file. I have modified for a more efficient process and more like the solution.

 

See attached. 


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

MsBindy
8 - Asteroid

I'm always pleased when mine is 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.

Amarendra
10 - Fireball

Attaching the solution for challenge 3. 


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