Reference DateTime for calculating occupancy

[dafnis14]

Hi All,

I would like to analyze occupancy percent  of a parking lot .

My data include entrance and exit timestamps of cars.

I generated reference timestamps for each hour in the range.

My question is which is the best time resolution (i.e., hour, half an hour..) to analyze the occupancy?

This is due to the fact that the entry and exit times can vary, and I'm not sure how should I round them.

Please consider such cases:

Receipt_EntryDateTime	Receipt_ExitDateTime
04/03/2016 11:35:00	04/03/2016 12:33:00

Receipt_EntryDateTime	Receipt_ExitDateTime
06/04/2016 11:43:00	06/04/2016 12:27:00

Thanks!

[ganeshsvm]

Please provide more information(Sample data & app) about your scenario & problem to get clarification.

-Ganesh

[dafnis14]

Hi Ganesh,

Attached are sample data.

Please note that multiple lines represent reference timestamps.

16_6581	01/02/2017 11:18:00	01/02/2017 13:22:00	01/02/2017 11:18:00
16_6581	01/02/2017 11:18:00	01/02/2017 13:22:00	01/02/2017 12:18:00
16_6581	01/02/2017 11:18:00	01/02/2017 13:22:00	01/02/2017 13:18:00

Thanks for your kind help!

[sfatoux72]

Hi Dafinis,

If you want to be cetain to have all variations of occupancy including max and min value, you need to have a sampling frequency 2 times higher than your max frequency Nyquist–Shannon sampling theorem .

In your case, you could identify change (entry and exit) each full minute. If you sample your data each minutes based on 30 secondes (...  -  10:58:30  -  10:59:30  -  11:00:30  -  11:01:30  -  ... )  you will have all variations. If you want to have less data, you could aggregate your sampling result by hour and take only the min, the max and average value.

[dafnis14]

Hi Sebastien,

I had a hunch that this isn't a case for mere mortals 🙂

So the idea of generating reference timestamps is not relevant for such a case?

I still can't figure how should i generate the timestamps to check the cars inside.

Thanks!

[sfatoux72]

Like that :

SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';


LET vMinDate = Num(Date#('31/01/2017', 'DD/MM/YYYY')); 
LET vMaxDate = Num(Date#('02/02/2017', 'DD/MM/YYYY')); 

SampleCalendarTemp: 
LOAD DayStart(TimeStamp($(vMinDate) + ((RecNo()-1)/60/24 + 30/60/60/24) + (IterNo() -1))) AS AddedDate, // Use this one as date 
  TimeStamp($(vMinDate) + ((RecNo()-1)/60/24 + 30/60/60/24) + (IterNo() -1)) AS AddedTimeStamp 
AUTOGENERATE 1440 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate)); 

SampleCalendar:
LOAD AddedTimeStamp AS MyTimeStamp, 
AddedDate AS myDate,
Hour(AddedTimeStamp) as MyHour,
Time(AddedTimeStamp, 'hh:mm:ss') as MyTime
Resident SampleCalendarTemp;

DROP Table SampleCalendarTemp;


RawData:
Directory;
LOAD Distinct
Receipt_Key_SiteID_ReceiptNumerator,
Receipt_EntryDateTime,
Receipt_ExitDateTime
FROM
[Sample data.xls]
(biff, embedded labels, table is Sheet1$);


Left Join(RawData)
IntervalMatch(MyTimeStamp)
Load Distinct
Receipt_EntryDateTime,
Receipt_ExitDateTime
Resident RawData;

[dafnis14]

Hi Sebastien,

This is a true masterpiece!

Much appreciated!

Obviously I wasn't in the right direction..

I just want to inquire about something that bothered me while using whole hours

Please note that parking that starts near the end of an hour (10:53) is considered as a whole hour (10)

How do such cases impact the results?

Thanks!

[MarcoWedel · Accepted solution]

Hi,

using some dynamic bin sizes / time intervals, your users might as well select the required time resolution on their own:

tabParking:
LOAD Distinct
     Receipt_Key_SiteID_ReceiptNumerator, 
     Receipt_EntryDateTime, 
     Receipt_ExitDateTime     
FROM [https://community.qlik.com/servlet/JiveServlet/download/1205140-263727/Sample%20data.xls] (biff, embedded labels, table is Sheet1$);

tabCalendar:  
LOAD *,
     Minute(DateTime) as Minute,
     Hour(DateTime) as Hour,
     Time(Frac(DateTime)) as Time,
     DayName(DateTime) as Date,
     Day(DateTime) as Day,  
     WeekDay(DateTime) as WeekDay,  
     Week(DateTime) as Week,  
     WeekName(DateTime) as WeekName,  
     Month(DateTime) as Month,  
     MonthName(DateTime) as MonthName,  
     Dual('Q'&Ceil(Month(DateTime)/3),Ceil(Month(DateTime)/3)) as Quarter,  
     QuarterName(DateTime) as QuarterName,  
     Year(DateTime) as Year,  
     WeekYear(DateTime) as WeekYear;    
LOAD Timestamp#(Timestamp(MinDateTime+(IterNo()-1)*'00:01')) as DateTime  
While MinDateTime+(IterNo()-1)*'00:01' <= MaxDateTime;  
LOAD Min(Receipt_EntryDateTime) as MinDateTime,  
     Max(Receipt_ExitDateTime) as MaxDateTime  
Resident tabParking;

tabDateLink:
IntervalMatch (DateTime)
LOAD Distinct
     Receipt_EntryDateTime, 
     Receipt_ExitDateTime
Resident tabParking;
Left Join (tabDateLink)
LOAD Receipt_Key_SiteID_ReceiptNumerator, 
     Receipt_EntryDateTime, 
     Receipt_ExitDateTime
Resident tabParking;

DROP Fields Receipt_EntryDateTime, Receipt_ExitDateTime From tabDateLink;

hope this helps

regards

Marco

[dafnis14]

Hi Marco,

Thank you very much for helping!

I would appreciate if you kindly check a specific issue:

When a parking starts on one day, and ends on another day (17_30898), the hours are not correct.

Thanks!

[dafnis14]

Hi Marco,

Thinking further, the idea is that the user will select a certain weekday date.

In such a case, your solution is perfect for me.

Thanks very much!