Hi guys, I am working on a parkeerstudy and I would appreciate your help. My question is for sheet 10, in the orizontal axis are hours (07:00, 08:00, 09:00 etc) and on the vertical axis are car plates, In sheet 10 I can see the duration of each parking event for instance 212D is parked from 08:00-12:00 and from 13:00-14:00. I'm trying to find a way to visualise those district parking events (the end result to be that car 212D parked there for 4 hours and 1 hour not 5 hours). Any ideas are very appreciated.
This required a fair number of relatively complex calculated fields, which I've provided below:
End Time
// End time of the event (1 hour later).
DATEADD('hour', 1, [tijdstip])
Event ID
// Uniquely identify each event.
IF MAX([tijdstip]) = LOOKUP(MAX([End Time]), -1) THEN
PREVIOUS_VALUE(0)
ELSE
IFNULL(PREVIOUS_VALUE(0),0)+1
END
Duration
// Event duration. Basically, we're creating a running total.
IF MAX([tijdstip]) = LOOKUP(MAX([End Time]), -1) THEN
PREVIOUS_VALUE(0)+1
ELSE
1
END
Start Time
// Start time of the overall event.
DATEADD('hour', -[Duration], MAX([End Time]))
Keep
// We wish to only keep the last record.
IF MAX([End Time]) = LOOKUP(MAX([tijdstip]), 1) THEN
"Not Last Event"
ELSE
// This is the last record in the event.
"Last Event"
END
We then build a view like this:
Then use Keep as a filter, keeping only "Last Event".
Table calcs to be computed like this:
End result:
You can also use this to create your gantt chart. It looks the same as yours except each gantt bar is a single bar, rather than individual 1-hour segments.
See attached.
