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!
Solved! Go to 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.
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.
Thank you, Marc
User | Count |
---|---|
40 | |
34 | |
28 | |
23 | |
17 |