cancel
Showing results for 
Search instead for 
Did you mean: 

Subtract a running value from the top of a column

hoffmanroni
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

TerryLynch
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

Works, thanks very much Terry

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!




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

Thanks Kevin