Weekly Challenge

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

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

Submit Feedback
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.

Challenge #3: Running Averages

Highlighted
Alteryx Certified Partner

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

Highlighted
Alteryx Certified Partner

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

Highlighted
Alteryx Partner

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.

Alteryx Partner

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

Highlighted
12 - Quasar

My solution for Challenge 3.

Spoiler
Weekly Challenge 003.png
Highlighted
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.

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

Highlighted
9 - Comet

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.

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

Highlighted
Alteryx Certified Partner

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.