Forum Discussion
Rolling hour with a trafiffic schedule that only gives me the actual departure time
See the attached. I was able to get the five minute count for each five minute period by using the FILTER function. But only for the raw data you provide.
For what it's worth, I've always understood a rolling total to refer to the prior months or hours or whatever, not the ensuing or following ones. So a twelve month rolling total (or rolling average) goes BACK in time to get the first number, and then the ensuing months up to the most recent. You appear to be interpreting rolling totals in the other direction. If that's what you desire, it works; I'm just not familiar with that way of looking at it.
So all I've done at this point, in the attached, is shown in the green area, and it uses the FILTER function to get the number of flights for each five minute period.
I'm not quite sure what is going on, but I did discover that there is a difference of some kind between the time as shown on the "Data Source" tab and the time as shown on the "Expected Result" tab. The only way I could get my FILTER function to work, in this formula
=SUM(FILTER('Data source'!$D$2:$D$29,'Data source'!$B$2:$B$29='Expected result'!B81,0))
was to go through the "Data Source" times and copy the corresponding times from "Expected Result" --I couldn't see a difference between the two fields (they're both the same length). They do both show up as 'custom format' so it's possible they're different custom formats. I'll let you look into that further. But certainly, that difference--whatever it is--will continue to affect your results until it's resolved.
You've had over 60 views without replies. I think your situation needs more explanation.
Specifically, what do you mean by "I need to analyze the data in rolling (24) hours"? You've given us the raw data that serves as an input; but what do you expect the output to look like? That's especially important, given that the raw/input data you've given as a sample only appears to cover 90 minutes of one 24 hour period.
- So one question has to do with the rolling 24 hours.
- Another would be what "the 5 minute rolling hour" is? How does it figure in the desired output?
- Furthermore, by "analyze" what do you mean"?
- Number of flights within each 5 minute period over the 24 hours?
- Number of flights by airline within each 5 min period?
- Etc?
I have uploaded what may be a better explenation. In Sheet 1 i have put some dummy data for a small time periode that represent data that i get from my source. The source gives me the actual time for either an arrival or departure, and when there is no movement for a certain time it is not part of the source data. In sheet 2 i have filters as in a pivot where i can select e.g year, month, day, type of traffic (international/domestic) and arrivals or departures. The time is in 5 minutes inkrements and a match from sheet 1 is displayed on the correct date, time with sum of movements for that date/time. If no match = 0. Then in rolling hours it is simply the sum of movements from e.g 00:00-00:05 and 01:00-01:55 for each 5 minutes intervall. Not sorted by airline, only a sum.
See the attached. I was able to get the five minute count for each five minute period by using the FILTER function. But only for the raw data you provide.
For what it's worth, I've always understood a rolling total to refer to the prior months or hours or whatever, not the ensuing or following ones. So a twelve month rolling total (or rolling average) goes BACK in time to get the first number, and then the ensuing months up to the most recent. You appear to be interpreting rolling totals in the other direction. If that's what you desire, it works; I'm just not familiar with that way of looking at it.
So all I've done at this point, in the attached, is shown in the green area, and it uses the FILTER function to get the number of flights for each five minute period.
I'm not quite sure what is going on, but I did discover that there is a difference of some kind between the time as shown on the "Data Source" tab and the time as shown on the "Expected Result" tab. The only way I could get my FILTER function to work, in this formula
=SUM(FILTER('Data source'!$D$2:$D$29,'Data source'!$B$2:$B$29='Expected result'!B81,0))
was to go through the "Data Source" times and copy the corresponding times from "Expected Result" --I couldn't see a difference between the two fields (they're both the same length). They do both show up as 'custom format' so it's possible they're different custom formats. I'll let you look into that further. But certainly, that difference--whatever it is--will continue to affect your results until it's resolved.
- Thank you. I will study your solution
