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

[arhellman]

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:

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: 

Here are the results:

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. 

[Bob_Blackey · Accepted solution]

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

[arhellman]

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.

[Bob_Blackey]

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!