Challenge #3: Running Averages

[JS1989]

Solution

[martinson]

Output file seems to be extremely buggy but I think I got the answer that it was looking for

[witleyn]

[solution]

[TGreen]

Solution for challenge.

Instructions were confusing, like the way zeros were imputed over nulls instead of using nearest value as stated. Also, the solution ends up miscalculating the second month of those running averages.

[JBevan89]

As the output file is really slowing things down, I give up on trying to reconcile, as I get the gest of the challlenge and have completed

[someotherguy]

@JBevan89 @martinson The text input tool for the output really struggles when it contains over 10k records, this one contains over 25k! maybe Alteryx behaved differently in 2016?

Either temporarily cache the tool or just replace directly with YXDB input.

[someotherguy]

There are some issues with this Challenge

1. the text tool for the output contains over 25k records, this really slows down alteryx when running the workflow.  It should be under 10k records for optimal performance.  SOLUTION: Create a temporary cache or convert to yxdb input or reduce record count.
2. The output file groups the values into three groups via the name field but when using the cross tab tool by default the period is replace with an underscore for the 3 month avg values.  SOLUTION: check the retain characters box in the tool.
3. The same thing happens to the 6 month avg values but then in the Join Multiple tool appends these values with 'Input_#3_'  SOLUTION: create a new field that labels the initial values, the 3 month avg and the 6 month avg, then move these values to column names and move the RM category to a single column.  This makes the solution much easier to read and reduces the output record count to 1.4k size to 40KB ~90% reduction.  Which would then fix the first problem. 

optimized output

Spoiler

 

[Bennu]

This seems to have been quite a contentious challenge due...

I chose to pro-rate averages for which there were missing prior values like months 1 and 2 for 3 month averages, and months 1 through  for 6 month averages.

And chose to display results in a grid for a more consolidated view, which was the part more challenging.

Here's my unsolicited piece of advice: do your own thing and don't over think it :)

Spoiler

pro rated 6month average formula.. maybe it could be done in some shorter way?
(iif(isnull([Row-5:Value]),0,[Row-5:Value]) +iif(isnull([Row-4:Value]),0,[Row-4:Value]) +iif(isnull([Row-3:Value]),0,[Row-3:Value]) + iif(isnull([Row-2:Value]),0,[Row-2:Value]) +iif(isnull([Row-1:Value]),0,[Row-1:Value]) +
iif(isnull([Value]),0,[Value]) )
/
(iif(isnull([Row-5:Value]),0,1) +iif(isnull([Row-4:Value]),0,1) +iif(isnull([Row-3:Value]),0,1) + iif(isnull([Row-2:Value]),0,1) +iif(isnull([Row-1:Value]),0,1) +
iif(isnull([Value]),0,1) )

Spoiler