Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-row sum over dynamic range

MLD
5 - Atom

Greetings.  I've got an alteryx-induced headache over this one.  Any ideas are much appreciated.

 

Data looks like this:

WeekStartDateQuantityWeeksToSum
1/1/20171102.1
1/8/20171072.8
1/15/20171073.5
1/22/20171173.5
1/29/20171202
2/5/20171011.8
2/12/20171183.1

 

The goal is to sum Quantity over the number of rows given in WeeksToSum, including the current row.  Expected results are as follows (with some helper columns added which split WeeksToSum into whole and fraction).

 

WeekStartDateQuantityWeeksToSumWholeWeeksPartialWeeksExpectedResults
1/1/20171102.120.1227.7
1/8/20171072.820.8307.6
1/15/20171073.530.5394.5
1/22/20171173.530.5397
1/29/2017120220221
2/5/20171011.810.8195.4
2/12/20171183.130.1118

 

The best idea we've come up with is:

  • For the WholeWeeks, create a multi-row formula with a large if/elseif statement.  if wholeweeks = 1, then current row, elseif wholeweeks = 2 then current + next, etc.
  • For fraction, use "offset" concept which has been shown in other alteryx posts.

 

To make the problem harder, above is just for 1 product.  The real data set has many products.  So a group by issue also applies.

 

Thanks for any ideas!

 

6 REPLIES 6
AmeliaG
Alteryx
Alteryx

Hi @MLD,

 

Thanks for your question! The best solution would be to use a batch macro. Here is a great training to get you started. This will allow you to "Group By" product. I would also recommend that you use a filter to filters the rows of data you need and then use the summarize tool. .

 

Hope this helps!

 

Amelia

MSalvage
11 - Bolide

@MLD,

 

I went with a Generating rows solution. This was a pretty fun one to think through. Thanks for the challenge.

 

Solution attached.

 

Best,

MSalvage

 

EDIT: Updated solution to account for multiple products.

AmeliaG
Alteryx
Alteryx

Hi @MLD,

 

I've attached a workflow here which contains a batch macro which will achieve your desired results (no long multi-row formula necessary). This macro is currently configured to 'GroupBy' the 'Product' thus this workflow will work with multiple products. Please note that your 'RecordID' should be set for each product, hence my using s Multi-Row formula tool to do this. 

 

Let me know if you have any questions!

 

Amelia

MLD
5 - Atom

MSalvage - Unbelievably elegant!  And such a fast reply.  Thanks!

 

 

MLD
5 - Atom

AmeliaG - I figured a macro solution had to exist to simplify the long (and not bullet proof) multi-row formula.  Thanks!

AmeliaG
Alteryx
Alteryx

@MSalvage Very elegant solution, indeed!!

Labels