Subtraction from multiple rows

[annanirvin]

Hi community.

Is it possible to subtract a sum, e.g. 525, through several subtotals from several different rows in a table? The sum per row must not be negative.

Date	Qty	Qty OUT
2019-04-13	67	0
2019-04-20	71	0
2019-04-19	103	0
2019-04-21	113	0
2019-04-22	183	12

I really appreciate all the help I can get.

[zhadrakas]

can you explain that step by step what you want to achieve?

[annanirvin]

It's a bit difficult to explain but I'll try 🙂

If we have 67 applications with start date 2019-04-13, 71 applications with start date 2019-04-20 etc. according to my example and I know that we have the capacity to handle 525 of them and that the oldest start date is handled first, I want to be able to calculate both the oldest start date for the remaining applications and how many applications remain.

(My example is simplified, in reality the volumes are considerably larger.)

[zhadrakas · Accepted solution]

here is a sample  that should do it:

TEST:
LOAD * INLINE [
Date, Qty, Qty OUT ,Capacity
2019-04-13, 67, 0, 525
2019-04-20, 71, 0 ,525
2019-04-19, 103, 0 ,525
2019-04-21, 113, 0 ,525
2019-04-22, 183, 12 ,525
];

Sort by Date ascending

here are the Expressions:
suM(Qty)
suM(Capacity)
RangeSum(Above(TOTAL Sum(Qty), 0, Rowno(TOTAL)))
[Cap Total] - [Qty kum]

regards
tim

[annanirvin]

Neat! That's exactly what I want. Thanks a lot!

Best regards
Anna