Challenge #3: Running Averages

[KOBoyle]

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]

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]

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]

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

[Philip]

My solution for Challenge 3.

Spoiler

[sagarb]

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]

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]

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]

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]

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.