Alteryx Designer

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

Multi-Row Formula, Previous Row is always taken as a 0 Value

Highlighted
5 - Atom

Hello All,

 

My Alteryx Version: 2018.2.4.47804

 

Been searching through the discussion boards for ideas on how to fix a hopefully simple problem.

I'm not as experienced in using the Multi-Row Formula, but I did check the guide and I think I understand how it's intended to be working but I'm still having issues with it.

 

I'm wanting to do some simple subtraction for previous row value minus the current (active) row value, however it's always taking the previous row value as a 0 instead of the actual. 

 

I'm sorting the values correctly before applying the multi-row formula, thinking it would help apply the formula as intended.

 Both the Value & Target are the same field type: Float.

 

Source Table:

Alteryx_MR_BaseTableHelp.jpg

What I'm trying to do:

There are multiple FlagA's and FlagB's. All with their own day counts. It's currently correctly sorted going into the multi-row formula.

At Row 1, I simply want to take 1 - Value ( 1 - .977816)

At Row 2, I want to take Row 1 Value - Row 2 Value (.977816 - .955632)

At Row 3, I want to continue this logic and take Row 2 Value - Row 3 Value (.955632 - .933448)

etc until it reaches the end of days for FlagA/Flag B Combination.

 

It will start over and do the same the next FlagA/B combination.

 

Here's the what's I'm setup as in Multi-Row Formula: Alteryx_MultiRowFormula.png

 

 

 

Here are the results:Alteryx_MultiRowFormula_Results.png

 

 


It's not taking the previous row value, and since the settings in the multi-row at set to 0 for row's that don't exist, it's taking 0 - active row value, instead of Row 1 Value - Row 2 Value.

 

 

Am I misunderstanding the multi-row formula tool, or do I have that  it improperly configured? Any help or idea's would be appreciated for this hopefully simple solution I'm not seeing. 

Highlighted
Alteryx Partner

It looks like you have too many values selected in the group by section.  (rule of thumb - if it says optional be cautious to check things),

 

It will reset the calculation for every unique set of grouped items, so since you check everything it is always starting over and the previous rows value is considered 0.

 

I would just check the FlagA and FlagB and try that.

 

Cheers,

Bob

 

Highlighted
5 - Atom

Thanks Bob, 

 

I didn't read about that in the guide, and somehow didn't try that specific combination by itself.

That seems a little counter-intuitive for how I imagined it treating a set of columns as unique, but it makes sense for how it's reading the data and why it's showing 0's.

 

Thanks again! I've marked it as solved.

Highlighted
Alteryx Partner

No worries.

 

BTW - thanks for the great documentation of the issue - it made it very clear and really helpful in figuring it out. It's a role model on how to post a question!

Labels