Updating earlier values to zero

[Jake5]

I have a workflow that is evaluating account values over a given date range.  On occasion there will be a scenario where an account value is > 0, then drops to 0, and then returns to > 0.  When those instances occur, I need Alteryx to update the earlier, positive values to 0 and only retain the most recent set of consecutive, positive values for a given account - fund.  Using the attached input file as an example, for account ABC - Fund1 I would like the 30.43 values (10/4 and 10/5) to drop to zero and retain the 31.08 (10/8 and 10/9) and 25.00 (10/10) values.  I included account XYZ - Fund 2 to point out the solution needs to perform this evaluation at the account-fund level.  Thanks.  

[CoG]

Here is a sample workflow that I believe accomplishes your requirement:

[Jake5]

Hi, Andrew.  Thanks for taking a look at this.  And my apologies as I didn't stage the data to best reflect the live scenario, which is that the final value for Account ABC is 0 (on 10/10).  When I update the input to show that value the logic breaks because the zero_index value now starts with 1 (for ABC).  See attached.  Do you know of a way to further tweak to account for this?  

[CoG]

Yes, but exactly how depends on whether the most recent data will always be zero, or if it can vary (sometimes 0, sometimes >0).

If always 0, just change the filter to zero_index > 1, and that should shift everything and work fine. If it varies the workflow will need to be modified slightly more.

[Jake5]

So the last value will always be 0, but I've encountered scenarios where the last few values are zero.  When I change 10/9 to 0, then it changes all values to 0 when that scenario should retain the 10/8 value of 31.08.  Any thoughts on how that can be tweaked to address?

[CoG · Accepted solution]

Ah... This should solve the problem then:

IF [Value] = 0 && [Row-1:Value]>0 THEN [Row-1:zero_index]+1 ELSE [Row-1:zero_index] ENDIF

Just stick this in the Multi-Row Formula Tool.

And make sure the Filter stays as [zero_index]>0

[Jake5]

Thanks, Andrew!  This worked.  I appreciate your patience in helping me through this one!