I have something that is puzzling me a the moment. I have data rows that have a start/arrival datetime and a finish/vacated datatime field. I have extracted the difference in minutes between the 2 datatime stamps and have created a new field which is based on the start time hour and i also have a time band field which uses the hour from the start/arrived time and shows as 0 - 1, 1 - 2 etc....
What i want to do is create a table/chart where i can see the total time between the time bands, i.e.
0-1 183 min
1-2 124 min
2-3 210 min
The problem i face is that i don't want to see all the time for that row against just the start/arrived hour dimension. So if the total time between start/arrived and finished/vacated is 180 min and the start hour is 9 then i would like to see the time apportioned over the time periods 9 , 10 and 11. so i would then see an acumulation of time over the seperate hour time periods -
9 - 10 60 min
10 - 11 60 min
11 - 12 60 min
rather than showing
9 - 10 180 min
Hope someone can point me in the right direction