Hello people,
I find myself stuck on the following problem:
I have some data from a traffic simulation where I have the data of individual vehicles per second (also see simpiflied example). There is a field containing every second per vehicle. The second field effectively is a collection of seconds (from the first field) to aggregate the data by. The third field contains unique ID codes per individual vehicle.
Each vehicle (v in the attached example) can have multiple timespans (I will call these 'timespans' for lack of a better explanation/word, r in the attached examples), meaning it can be in found in the simulation network during that time. I want to allocate each vehicle to the maximum timespan (r) in the dataset for analysis purposes, BUT! Only if it is part of that timespan (r) for equal or more than 2 seconds.
I made a straight table chart with the timespans (r) as dimension, and then using the following expression to count the distinct amount of vehicles (v) that are allocated to that timespan:
Count(DISTINCT If(r=Aggr(Max({1}r),v),v))
This works fine, but I don't want to just allocate the vehicles (v) to the maximum timespan (r), but the maximum timespan of which that vehicle is part for 2 or more seconds (t). I've tried the following expression, which gives an error and no result:
Count(DISTINCT If(r=Max({1}If(Aggr(Count(t),v,r)>=2,r)),v))
I already found out that the error is a result of the lack of a TOTAL qualifier, but I cannot get any results (or at least not those I am looking for)
Please find an example dataset / expressions in the attached example.
Any help will be greatly appreciated!
Greetings,
Daniel
