Most efficient way to calculate and remove overlapping timelines from one timeline in Spotfire

Question

I have a question about timelines in Spotfire.

I have a script that takes two timelines from several machines and tasks.

-One timeline shows all the task times from several tasks per machine -One timeline shows the issues that occurred per machine.

With this data it will calculate the overlap time where the issues overlap with the task time, it does this per task per machine.

The script (TERR script) that i use now takes about 30+ minutes to calculate (the amount of data is over 100.000 lines) This is to much time and to much strain on the servers as well.

is there any way, perhaps not with a script but only with calculated columns (where you can intersect over the correct machine/tasks?) to do this more efficiently ?

This is an example of the outcome i'm looking for:

Task Start	Task Stop	Issue Start	Issue Stop	Overlap
2023-06-20 20:00	2023-06-21 01:00	2023-06-20 21:00	2023-06-20 22:00	60
2023-06-20 20:00	2023-06-21 01:00	2023-06-20 23:00	2023-06-21 00:30	90
2023-06-21 23:00	2023-06-22 02:00	2023-06-21 22:30	2023-06-22 00:30	90
2023-06-21 23:00	2023-06-22 02:00	2023-06-22 00:30	2023-06-22 01:30	60

this is a very simple table because it also needs to take in account the separate tasks and machines.

Hopefully someone can find a better solution, thanks!

Answer 1

I understand you want to calculate the overlap column? Can you try this:

case  when not (([Issue Stop]<[Task Start]) or ([Issue Start]>[Task Stop])) then 
  DateDiff('minute',Max([Task Start],[Issue Start]),Min([Task Stop],[Issue Stop]))
else 0.0 end