Help needed with ValueLoop example

[dklassen]

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

Measure:

  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.

Thanks for any advice.

[marcus_sommer]

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.

- Marcus