Compare expression in calculated rangesum

Wed, 25 Nov 2020 16:16:04 GMT

Hello everybody,

I want to calculate a cumulative sum, but I really do not find the way it works. Each record represents an event of a bus which arrives at a given station for a given time and it says when the next departure of this bus is. For each timestamp a bus arrives, i want to know, how many busses already stand at this station. So my approach is to use a RangeSum to count all arrivals before the actual arrival-timestamp and subtract the count of the next departures which are also before the actual arrival-timestamp.

For example, I have the following records

Id, Arrival, NextDepartue, Station

1, 13:05, 13:05, A

2, 13:40, 18:16, A

3, 16:20, 17:05, A

4, 17:00, 17:00, A

5, 18:30, 18:35, A

Before arrival of bus with Id 1 there are 0 busses at the station A,

before arrival of bus with Id 2 there are 0 busses too because bus with Id 1 leaves directly,

before arrival of bus with Id 3 there is 1 bus because bus with Id 2 leaves at 18:16,

before arrival of bus with Id 4 there are 2 busses because bus with Id 2 leaves at 18:16 and bus with Id 3 leaves at 17:05,

before arrival of bus with Id 5, there are 0 busses at station A because all other busses already left.

My question is: is there any possibility to compare the expression in a rangesum with the actual line, which is not part of the rangesum? I have tried something like the following:

Rangesum(Above(Count(Arrival), 1, RowNo() - 1)) - Rangesum(Above(Count({$<NextDeparture = {"> Arrival"}>} NextDepartue), 1, RowNo() - 1))

And in the Set Analysis I want to compare each value of NextDeparture of the above records with the value of arrival of the actual record(which is not part of the rangesum!), but I think it does not work as desired.

Can anybody help? Maybe with a different approach?

Re: Compare expression in calculated rangesum

Gysbert_Wassenaar — Mon, 14 Mar 2016 11:45:47 GMT

See attached example.

Re: Compare expression in calculated rangesum

Mon, 14 Mar 2016 12:05:37 GMT

I did not have the idea to calculate the sum in the datamodel. Thanks a lot.

topic Compare expression in calculated rangesum in QlikView

Compare expression in calculated rangesum

Re: Compare expression in calculated rangesum

Re: Compare expression in calculated rangesum