
Re: Traffic simulation data analysis
Dariusz Mielczarek May 16, 2014 9:06 AM (in response to D S)once again:
for you calculation interesting are only those rows, where r>=2??

Re: Traffic simulation data analysis
D S May 19, 2014 3:52 AM (in response to Dariusz Mielczarek)Hey there,
What I'm trying to achieve there is to alocate the vehicle (v) to the highest timespan (r) of which it is part at least 2 seconds. I count the seconds (t) and make sure it is 2 or higher, hence the >=2. I know I can simply Count(t), because the dataset only contains unique values for every vehicle (v) in this field, and that every second is in there once.
Re: Traffic simulation data analysis
Dariusz Mielczarek May 19, 2014 4:14 AM (in response to D S)If i understand, for example for r=1 you expect result 2,
because there are vehicles 1,2,3,6, but vehicles 3 and 6 were only 1 time (1 sec).
regards
Darek

Re: Traffic simulation data analysis
D S May 19, 2014 4:24 AM (in response to Dariusz Mielczarek)vehicle 3 and 6 should indeed not be alocated to r=1, but not because of the time they are part of it. They are part of later timespans (r) for 2 seconds and thus should be alocated to those. The total expected results in a straight table with r as dimension would be:
r, count(v), concat(v)
1, 1, (2)
2, 2, (1, 3)
3, 1, (4)
4, 0, 
5, 1, (6)
In the example the only vehicle for which the result between checking for the max(r) of which it is part for at least 2 seconds would be vehicle 4, which is part of r=4 for only 1 second and is thus alocated to r=3.

Re: Traffic simulation data analysis
Dariusz Mielczarek May 19, 2014 4:31 AM (in response to D S)ok, let me try again


Re: Traffic simulation data analysis
Dariusz Mielczarek May 19, 2014 4:30 AM (in response to Dariusz Mielczarek)this expression:
sum(if(aggr(Count(DISTINCT t),v,r)>=2,1))
with r as dimension
should give result, i think, you need

Re: Traffic simulation data analysis
D S May 19, 2014 4:43 AM (in response to Dariusz Mielczarek)The expression you propose will alocate each vehicle (v) to each timespan (r) of which it is part for at least two seconds, not just the latest
I'm trying to alocate each vehicle distinctly to only the highest value of r of which it is part for at least 2 seconds. This is why my formula contains the Max() function.
When I look in the qlikview help at the advanced aggregation section it states the requirement for the TOTAL qualifier. But using it will result in in alocating each vehicle to the overal Max(r)=5, and thus resulting in no (or wrong) results.
For example, when using
Count(DISTINCT If(r=Max(TOTAL If(Aggr(Count(DISTINCT t),v,r)>=2,r)),v))
the bold part of the function will check r (the dimension) against the Max(TOTAL r), which is 5 for each vehicle because of the TOTAL qualifier. Because only 1 vehicle is actually part of r=5, the expression will only yield the correct result on the last row of the table, showing 0 for each other row. Without the use of the TOTAL qualifier however it will yield no result at all.

Re: Traffic simulation data analysis
D S May 19, 2014 4:59 AM (in response to D S)I've just found the answer!!
I forgot to aggregate the Max() using NODISTINCT for proper results
This one works:
Count(DISTINCT If(r=Aggr(NODISTINCT Max({1}If(Aggr(Count(DISTINCT t),v,r)>=2,r)),v),v))
Thank you very much for taking the time to help me out with this problem
Kind regards,
Daniel


Re: Traffic simulation data analysis
Dariusz Mielczarek May 19, 2014 5:32 PM (in response to D S)Hello Daniel,
you may also use:
expression:
count(DISTINCT v)
dimension:
=aggr(max(aggr(NODISTINCT if(count(DISTINCT t)>1,r),v,r)),v)
with "Suppres when value is null" checked.
regards
Darek





