If the idea is to only get maximum amount of overlaps for location, you could first calculate all overlaps by adding start and end times to a matrix and then sorting it while keeping track what is end and what is start. Then you can calculate how many values fit between each start-end pair.
Maybe something like this will work (might have some problems with duplicated values):
Names Default To Here(1);
dt = Current Data Table();
dt << New Column("Overlap", Numeric, Continuous);
dt << Sort(By(:"Start time (UTC)"n), Replace Table, Order(Ascending));
starts = dt[0, 1];
ends = dt[0, 2];
m = Sort Ascending(starts |/ ends);
For Each Row(dt,
start = :"Start time (UTC)"n;
end = :"End time (UTC)"n;
:Overlap = Loc(m, end)[1] - Loc(m, start)[1] - 1;
);
/*
dt << New Column("MaxOverlap", Numeric, Continuous, << Set Each Value(
Col Max(:Overlap, :Location)
));
*/
tab = dt << Tabulate(
Show Control Panel(0),
Add Table(
Column Table(Statistics(Min, Mean, Max), Analysis Columns(:Overlap)),
Row Table(Grouping Columns(:Location))
)
);
You should be able to do this also almost interactively in JMP by creating new datatable with three columns: Time, TypeOfTime(start,end) and original row number. Then sort it by ascending and write a formula to check for overlaps (Could maybe use Lag(), I used Col Min(Row(), :Row) and Col Max(Row(), :Row()) and their difference). Finally create summary table to get max value / join back to original table based on row numbers.
Summary (didn't add max here):
There seems to be slight differences between the two methods, so maybe one of them is wrong. Maybe caused by duplicated values (I did also delete missing values from interactive method)
Much easier to look for possible issues when you have data table for it:
Also the interactive method can be scripted very easily with JMP16's enhanced log.
Edit: This most likely has following issues: 1. if one time starts before checked start time and ends after checked end time, it won't be calculated and 2. if the time starts and ends between checked times, it will be calculated twice. Second one should be fairly easy to fix but the first one will require a bit more.
Example of 2. OriginalRowOrder starts and ends inside OriginalRowOrder 10 and gets calculated twice:
Example of 1. OriginalRowOrder 13 starts and ends outside 14:
Edit2: Didn't bother figuring out how those could be taken care of interactively so I fairly quickly wrote two formulas (hopefully they are correct...). Missing values must be removed / recoded before using these. OverlapsOutside might get a bit slow if done with formula without any additional optimizations.
OverlapsInside (should solve problem2):
N Items(Associative Array(:OriginalRowOrder[Index(:MinRowForRow, :MaxRowForRow)])) - 1
OverlapsOutside (should solve problem1):
If(Row() == 1,
m = :OriginalRowOrder << Get As Matrix
);
Try(
starts = Associative Array(m[Index(1, :MinRowForRow - 1)]);
ends = Associative Array(m[Index(:MaxRowForRow + 1, N Rows())]);
starts << Intersect(ends);
N Items(starts);
,
.
);
OverlapsInside might be enough, depending on how you want to calculate overlaps. If you need both, then just get sum of those values.
-Jarmo