I have a few questions regarding calculations using time and time interval
I have some data that describes a few trains on a particular part of track over a few months. I would like to analyse the performance of these trains compared to their timetable. This involves determining the average speed and the time of the journey as well as the time between several points, lets call them platforms. I have been able to chart the average speed of the trains at each point (Average train speed sheet) and the average time interval of each train ID, some will have many as they run every week (Timing intervals sheet), I have excluded 2 trains for now as they appear to have outliers which will need further investigation. However, I would like to see an average time interval for all trains. I've tried calculating the average of average using the table calculations, but it only applies it to each train ID, I would like a single line for all trains.
I would also like to be able to see an average running total of the transit time. This works fine when there is only 1 train for the full period, but not when there are multiple trains with the same ID, they just sum up all the values. I would like to see a running total of the average interval, ie sum(avg(interval)). The total travel time should be between150 and 200 minutes, as you can see in the transit time sheet, this is only true for 3 trains. This is because there is only 1 train with this ID in the data. How do I get the average interval for trains with the same ID and then get a running total?
I would also like to get a time interval plot for the time between each platform, this will be over varying distances. This will be the main metric of comparison. This will be the sum of the intervals between the two platforms, but I'm not sure how to do this. I could create a list of platforms and their distances to check against but I'm not sure how to use this to calculate the correct time interval in Tableau. In excel this would just be a vlookup and a sum.
public train data.twbx 4.5 MB