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?
