topic Re: Subtraction from multiple rows in QlikView

Subtraction from multiple rows

annanirvin — Thu, 25 Apr 2019 12:58:23 GMT

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.

Re: Subtraction from multiple rows

zhadrakas — Thu, 25 Apr 2019 13:04:28 GMT

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

Re: Subtraction from multiple rows

annanirvin — Thu, 25 Apr 2019 13:39:37 GMT

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.)

Re: Subtraction from multiple rows

zhadrakas — Fri, 26 Apr 2019 10:53:08 GMT

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

Re: Subtraction from multiple rows

annanirvin — Sat, 27 Apr 2019 06:07:57 GMT

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

Best regards
Anna