topic Re: Challenge #3: Running Averages in Weekly Challenges

Challenge #3: Running Averages

GeneR — Tue, 13 Sep 2022 13:43:36 GMT

We hope you enjoyed last week's challenge. For the third challenge, let’s look at creating 3 and 6 month running averages.

The goal is to create 3 and 6 month running averages for the values contained in columns: c.LK98, p.LK98, c.1K, p.1K, c.NLP3, and p.NLP3. Create the averages by RM Category.

Update: As of 9/20/19, the start and solution files were updated. Your solutions may not look like those posted by Community members prior to this date.

Re: Alteryx Weekly Exercise - Week #3 Running_Averages (Intermediate)

martinbosak — Mon, 30 Nov 2015 16:39:04 GMT

is this output set correct?

for example row 73 - 6 months averages looks corrupted (not based on HP category)

btw excelent exercise! I really want to see original solution next week.

Thank you

Martin

Re: Challenge #3: Running Averages

Wadim — Fri, 27 Sep 2019 23:31:50 GMT

Thanks for your week's challenge, but there are some points I have to criticize in your solution:

1. There is a misstake for example on row 139-140/r3mo_d_CAGI there is set a row of "Null" which should be valid data.
The mainproblem is that "Null + x" always returns "Null" as result.
So Null fields have to be replaced with 0 and then reset back to "Null".

This problem occurs in the whole datasheet.

2. In the solution there are a lot of rows set to 0 which should be "Null" for example row 213-216.

I got a solution which hopefully covers those bugs, but it is not a sizeable solution and very much code has to be writen.
Im gonna attach the solution, if you find any misstakes or got a easyer way to solve it, please tell me.

I hope for a sizeable solution, from alteryx, because without I doubt to use it in terms of bussines.

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

Re: Challenge #3: Running Averages

mceleavey — Fri, 27 Sep 2019 23:31:52 GMT

Ok, I little bit of brute force but I got there:

Spoiler

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

Re: Challenge #3: Running Averages

SeanAdams — Fri, 27 Sep 2019 23:31:56 GMT

Hi all,

A lot of similarities in my solution compared to the provided solution - main differences are:

- Using the "Average" function with closest value gives the wrong answer for the second month of every product for the 3month; and for rows 2,3,4,5 for the 6 month

- Only accurate way to do this is to do a multi-row formula to do a row-count; and a multi-row formula to do a sum; and then a formula on each row to work out the average (sum/count)

- I used a formula to rename fields rather than dynamic rename - but I prefer the dynamic rename

Example of the difference:

Average function using multi-row with "Closest value" set to deal with missing rows

Row 1: 1 - 3 month average is 1

Row 2: 2 - correct 3 month average is 1.5 but the "closest value" method gives (1+2+2)/3 instead of (1+2)/2

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

Re: Challenge #3: Running Averages

MarqueeCrew — Fri, 27 Sep 2019 23:32:07 GMT

Looks like the sample output has at least 1 truncated column name.

Cheers,

Mark

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

Re: Challenge #3: Running Averages

NicoleJohnson — Fri, 27 Sep 2019 23:32:11 GMT

My solution.

Spoiler

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

Re: Challenge #3: Running Averages

Laurap1228 — Fri, 27 Sep 2019 23:32:20 GMT

Challenge #3 complete!

Spoiler

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

Re: Challenge #3: Running Averages

Natasha — Fri, 27 Sep 2019 23:32:22 GMT

My solution is also 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.

Re: Challenge #3: Running Averages

estherb47 — Fri, 27 Sep 2019 23:32:24 GMT

Also a similar solution to many above

Spoiler

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

Re: Challenge #3: Running Averages

DE0413 — Fri, 27 Sep 2019 23:32:37 GMT

There may still be an error in your solution file. When I compared my solution to yours, all of my 3-month calculations tied; however, my 6-month calculations did not always tie, and the variances were always happening in months 1-5.

In researching, I found that your 6-month multi-row formula tool is not grouping by HP Category and Name like the 3-month multi-row formula tool does; therefore calculating 6-monyh averages across HP Category and Name (or Measure). When I re-ran your solution file with HP Category and Name selected in the group by section of the 6-month multi-row formula tool, I was able to tie my solution output to the re-run output.

Please see my attached solution for the comparisons. If there is something you see that I have done incorrectly, please let me know.

!!!!Also, can someone tell me why my Alteryx attachments only open up as html while other people's attachments open up the file in Alteryx?!!!

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

Re: Challenge #3: Running Averages

NicoleJohnson — Thu, 04 May 2017 15:59:27 GMT

Hey @DE0413! I had this same issue a couple months ago, and I believe it has to do with the internet browser you are using to upload your workflows... Try using something other than Internet Explorer (I have used Firefox and Google Chrome, and both worked fine). Let us know if that does the trick! :)

Re: Challenge #3: Running Averages

DE0413 — Thu, 04 May 2017 16:01:11 GMT

Thank you Nicole--I will give that a try.

Re: Challenge #3: Running Averages

DE0413 — Thu, 04 May 2017 16:08:11 GMT

When I opened the community in Google Chrome, my attachments downloaded correctly. Thanks again Nicole!

Re: Challenge #3: Running Averages

Phil_L57 — Fri, 27 Sep 2019 23:32:39 GMT

Hi,

I created 2 Batch Macros to solve this challenge. One Macro to calculate the 3 month moving average and another to calculate the 6 month moving average.

The batch macro updates the field names within the expression of the Multi-Row tool so that all fields can be run in one tool.

I joined the two data streams by row position to produce the final output

Phil

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

Re: Challenge #3: Running Averages

LordNeilLord — Wed, 10 May 2017 10:45:56 GMT

This was a great introduction to the Multi-row formula tool

Re: Challenge #3: Running Averages

ydmuley — Fri, 27 Sep 2019 23:32:42 GMT

I must agree that, this was a challenging one! (At least for me).

But as they say, with challenges comes opportunity, got an opportunity to learn some new concepts on running averages.

My solution is more like others, specifically @Laurap1228.

I tried to leverage the running average formula given in the demo for the tool "Multi Row-Formula".

However, when I checked solution from @SeanAdams I was amazed to see that he correctly figured out the running average issue. If I had to go with the real averages not matching to the solution provided, I would without a doubt prefer Sean's solution. I must say, great findings @SeanAdams

Attached is my solution.

Thanks,

Yug

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

Re: Challenge #3: Running Averages

raj_k — Fri, 27 Sep 2019 23:32:48 GMT

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.

Re: Challenge #3: Running Averages

LordNeilLord — Thu, 01 Jun 2017 11:50:07 GMT

Workflow

Re: Challenge #3: Running Averages

irisTZ — Fri, 02 Jun 2017 20:24:09 GMT

Solution for #Challenge 3