Hi, I'm a Set Analysis rookie stuck on an example from the transportation industry.
Given a number of trips that have fields TRIP_ID, TRIP_START and TRIP_END where those are times but represented as an integer number of seconds, I'd like to create a chart that shows time as the dimension and the number of trips on the road as the measure. I feel like the following expressions should do the trick but isn't working:
Dimension: ValueLoop(0,86400) // 86400 is the number of seconds in a day
Sum(Aggr(if(TRIP_START <= ValueLoop(0,86400) and TRIP_END >= ValueLoop(0,86400), 1, 0), [TRIP_ID]))
From my research, it seems like Aggr and ValueLoop don't work well together, but I haven't been able to find an alternative.
I suggest to use an IntervalMatch to match your start/end to a master-calendar and master-timetable How to use - Master-Calendar and Date-Values. This meant to build a more suitable datamodel where your TRIP_ID is connected to them and to the rest of your datamodel. After this a count(TRIP_ID) should be enough to get your wanted results.