Updating earlier values to zero

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.

Second Investment Scenario.yxmd

Data Investigation

Accepted answers

CoG

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

All comments

CoG

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

Sample Workflow.yxmd

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?

Sample Workflow.yxmd

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?

Sample Workflow.yxmd

CoG

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703