cancel
Showing results for
Did you mean:

## Subtract a running value from the top of a column

New Contributor III
Hello, does anyone know how to do the following?

I have a column

x
--
5
6
10
20

and I want to subtract 14 for example

x
--
5 --> remaining is greater than row value, 14-5 leaves 9
6 -> remaining is greater than row value, 9 - 6 leaves 3
10 -> remaining is less than row value 10 - 3
20

so result would be

x
--
0
0
3
20

5 REPLIES 5
New Contributor II

select col-deltas 14&sums col from t

Or, making it a function

select {x-deltas y&sums x}[col;14] from t

Terry

New Contributor III
Works, thanks very much Terry
New Contributor III
Hello , trying to do something similar to this and getting stuck

I have two columns
t:([]price:"F"\$();qty:"I"\$())

price qty
10 100
20 300
30 400
22.5 1000

I want to get the sum of price*qty but only for a specific quantity off the top and just the value.  So for example if I use 200 then I want (100*10 +100*20).  Or if I use 500 then (100*10 + 300*20 + 100*30).  Is there a simple way to do this that I am missing?

Thanks!

New Contributor
Hi Roni,

You could use something like:

update total:price*remainder from update remainder:deltas 500&sums qty from t

Have added the remainder column for clarity.

Hope this helps
New Contributor III
Thanks Kevin