community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!

Challenge #3: Running Averages

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

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

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

Alteryx Partner
 
Quasar

My solution for Challenge 3.

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

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

Asteroid

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. 

Asteroid

I'm always pleased when mine is similar to others.  :)

Asteroid

Attaching the solution for challenge 3.