Sum specific rows in an account history in between of the appearance of a zero

[stuggijo]

Hello everyone

I would like to get a inbetween sum of a table.

Let me explain:
I have a table with different items (A, B, C…, below colored in light yellow, blue and white rows). The Items were added by date when a transfer is done. So I have the item A, and the bought or sold Items (Transfered_pieces). Beside that I have the “accumulated_pieces”, which is the current amount of pieses in my “warehouse”. Beside that I have the (selling/buying) price and the transfer sum (which is the transfer amount of pieces * price).

What I want to calculate in AppSheet is the column “Accumulated_value_of_item” (bright yellow marked).
That means: I want to sum all the rows of “Transfer_value” of the same item (A, B or C) of my current row, up to the last row before my current row (_this.row), where the amount of “Accumulated_pieces” wss 0 (zero).

So if the amount of pieces in the warehouse (“Accumulated_pieces”) is 0, which I have marked red, the sum process needs to restart with the next supply till the next “0” apears. (As shown in the table.) So the accumulation till the last 0 of the current Item is needed.

Thank you for your help!

[Marc_Dillon · Accepted solution]

SUM(
  SELECT(
    table[column] ,
    AND(
      [_rownumber] <= [_THISROW].[_rownumber] , 
      [_rownumber] > MAX( table[_rownumber] , 
                          AND( [_rownumber] < [_THISROW-1].[_rownumber] ,  
                               [accumulated_pieces] = 0 
                             ) 
                        )
       )
  )
)

Something like that. Sorry for the mess, hope you get the idea.

In English:
Sum up all of column where rownumber is between this row and the previous 0.

[stuggijo]

Thank you, Marc