This content has been marked as final. Show 2 replies
I have a pretty specific calculation question. I am working with some traffic camera data, and my database has total vehicles and average speed by hour and zone (lane). This camera data shows 2 lanes in the area of interest, so there are 2 rows in the data per hour.
I want to create a view of days and vehicles that filters on speed thresholds (I am using a parameter). So as you slide the speed up or down, the view shows the number of days in a month where it dips below, as well as the total volume of vehicles on those days.
Right now, the view I have shows the days value I want but not the vehicles. The days calculation is taking distinct dates where speed drops below the parameter set. However, since the data is split by lane, the vehicle calculation is only summing individual lanes where speed dips below the parameter set.
For example, in my current view, there are 6 days in April where the speed dips below 20 mph at 10pm (22:00). This happens on April 1, 2, 19, 23, 26, 27. Both lanes in days 19, 23, 27 have average speeds dipping below 20 mph, but in the other 3 days, only one lane met the speed threshold criteria. So in this example, the current vehicle calculation shows 4,923 which is missing 2nd lane data from those 3 days. The correct value should actually be 7,046, which would be including both lanes over all 6 days.
Can someone help me correct this calculation?