I'm not sure I fully understand what you are trying to achieve, but I have come up with a solution that might work for you. Give this a try:
Create a calculated field that picks up the start date-time from the previous record:
Create a calculated field that picks up the finish date-time from the previous record:
Create a calculated field to determine if the start date-time of this field is the same as the finish date-time of the previous field:
Create a calculated field to calculate the total duration:
Now, on the viz, place the [InitialStart], [Start], and [Lot] on the Columns Shelf, and place [TotalDuration] on the Rows Shelf:
For the [InitalStart], and [TotalDuration] fields, make sure to select Compute Using "Table (across)":
Place the [CurrentStartEqualInterimFinish?] on the Filters shelf, and make sure it is also Compute Using "Table (across)", and set the filter to just show the "True" values. And last, for the [Start] field on your columns shelf, be sure to un-mark the "Show Header" option, so that it is hidden. Then your final viz should look like this:
Hope that's helpful. Please let us know if this addresses your goal.
MullinSample_modified.twbx 112.7 KB
Sorry for the delay in replying. I've been working on what you've given me. You absolutely have the right idea and your modified workbook is brilliant.
Unfortunately the data source isn't as tidy as it ought to be. If you look at sheet 5 you'll notice that Lot 4159465 is repeated, but 4159662 only has a value of 388 seconds. This is due to the 4159662 changeover taking place over 4 rows in the dataset, as you can see in my original image. Is it possible to have a Lookup that can detect these odd sequences?
I also have a section in the original image that is highlighted red. This is where the finish time doesn't exactly match the next start time. Unfortunately this also occurs quite often.
I'm going to carry on working on this using your methods as a jumping off point. You've taken me further than I was able to get yesterday. Hopefully I can figure it out, but I would appreciate any extra help. I don't like to think something isn't possible...