Subtract a running value from the top of a column

Community Support
1

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

2

select col-deltas 14&sums col from t

Or, making it a function

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

Terry

3

Works, thanks very much Terry

4

Hello , trying to do something similar to this and getting stuck

I have two columns 

t:(price:“F”$();qty:“I”$())

priceqty

10100

20300

30400

22.51000

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!

5

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

6

Thanks Kevin