Reset Cumulative Count Based on a Column Value - Profit and Loss Sheet

[LorenzoFerreyra]

I have a typical profit & loss data (in Spanish). Profit = "Ganancia", Loss = "Pérdidas". Resultado = "Ganancia - Pérdida"

I need to have a running count for days when the day was positive (profit was greater than loss), but I need it to reset based on the value of another column.

The total will reset to zero and then continue.

Positive conteo (positive count) is a binary column that tells if the result of the day was positive (with 1 and 0). 

Then, in the remaining column ("dias seguidos con ganancia"), I make a running count that sums the positive days..

My problem is that I need to somehow reset it to zero or based on the binary column. That is to say, I have to start over the counting when we find a day whose value was 0 in column positive conteo

I have attached a picture to show what I was trying to do but I still can't understand how to do it on AppSheet... Currently, if you look at the picture, it is working (though not properly), since it counts positive result days, but it doesn't reset when there is a negative day.

Here are the formulas too:

Virtual Column Positive conteo formula: 

IF([Resultado] > 0, 1, 0)

Virtual Column dias seguidos con ganancia, i. e., running count:

SUM(SELECT(Positivos[Positive conteo],
AND([_RowNumber]<= [_THISROW].[_RowNumber],
[Positive conteo] = 1)))

[AleksiAlkio]

In your formula SUM(..) you are not checking where it should start the counting, meaning.. it doesn't care if there are rows with 0 or 1. You would need to check what is the latest 0 before this row and then start the counting from the next row.

[LorenzoFerreyra]

Hey, Aleksi.

Thanks for your reply.

Sorry, I don't get it.

I dont think I need to search for the last 0.

I just need the counting to reset when a 0 appears in the binary column.