Mean Time Between Stops

[spikenaylor1]

Need to calculate and display the mean time between stops in the Chart/Straight Table.

I have Equipment, Date of Stop, Time of Stop.

lots of different equipment, so I need to either in the script identify the equipment and the previous row that equipment is on to give me the interval, or can I do it in the chart table. i.e once filtered down to the equipment, I can then see the interval between stops and then work out the mean for that equipment.

hope this makes sense,  can anyone give me a helping hand or starting point.

[jwaligora]

Hi.

First, its probably a good idea to merge date and time into a single timestamp column - this will make the calculations (subtraction) easier. Just add them once the formats are deciphered and then wrap in TimeStamp() function.

=timestamp(date#('2023-10-09','YYYY-MM-DD')+time#('15:35','HH:mm'))

If you want keep the calculation logic in the front end, take a look at the Above() function ...also check the link to the corresponding Below() and maybe RangeAvg() functions at the bottom of that page. 

Handling it in the backend will make it less flexible in case users choose to filter the data on alternate dimensions (e.g. driver name?), but if equipment is the only relevant criteria, then backend is probably a better bet. It's likely easiest to just extract each equipment type into a separate table. If you don't yet have unique row IDs, manufacture some (see recno() and rowno() functions). For the calcs, check the Previous() function. Then rejoin the difference column back to main table. Averages can be easily and flexibly in the front end once the intervals are calculated. 

Remember to sort by timestamp before calculating differences. You may also want to reference the Interval() formatting option at some point.

These are pretty high level 'starting points'; hope they're not overly vague.

J.