topic Re: Reference DateTime for calculating occupancy in QlikView

Reference DateTime for calculating occupancy

dafnis14 — Thu, 02 Feb 2017 13:19:35 GMT

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!

Re: Reference DateTime for calculating occupancy

ganeshsvm — Thu, 02 Feb 2017 13:44:57 GMT

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

-Ganesh

Re: Reference DateTime for calculating occupancy

dafnis14 — Thu, 02 Feb 2017 14:24:15 GMT

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!

Re: Reference DateTime for calculating occupancy

sfatoux72 — Thu, 02 Feb 2017 15:41:29 GMT

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.

Re: Reference DateTime for calculating occupancy

dafnis14 — Thu, 02 Feb 2017 16:43:55 GMT

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!

Re: Reference DateTime for calculating occupancy

sfatoux72 — Thu, 02 Feb 2017 17:42:18 GMT

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;

Re: Reference DateTime for calculating occupancy

dafnis14 — Thu, 02 Feb 2017 19:03:35 GMT

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!

Re: Reference DateTime for calculating occupancy

MarcoWedel — Thu, 02 Feb 2017 23:40:18 GMT

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

Re: Reference DateTime for calculating occupancy

dafnis14 — Fri, 03 Feb 2017 04:57:34 GMT

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!

Re: Reference DateTime for calculating occupancy

dafnis14 — Fri, 03 Feb 2017 05:18:55 GMT

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!

Re: Reference DateTime for calculating occupancy

sfatoux72 — Fri, 03 Feb 2017 09:10:22 GMT

Hi Marco,

I found just a little problem, but I found the solution too 😉

The problem

If you consider only one receiptKey (that start en end in the same day) with a BinSize 00:15 like that, the first bar is not correct :

The reason

It is due to the expression of the calculated dimension :

=Time(Floor(Time,vBinSize),'hh:mm')

The value used as a step by the floor function is not a finite value (00:15 ==> 0.010416666666666666666 ...), but since it is the finite value 0.010416666666667 that is actually used, there is in the end A slight offset that causes the problem found.

The solution

We need to work with minute in this expression :

=Time(Floor(Hour(Time)*60+Minute(Time), Hour(vBinSize)*60+Minute(vBinSize))/24/60,'hh:mm')

Like that the step is a finite value 15 and we have a correct result :

Re: Reference DateTime for calculating occupancy

MarcoWedel — Fri, 03 Feb 2017 22:34:47 GMT

Hi,

yes, due to the limited resolution of the underlying numerical values, time arithmetics may show this behaviour.

Another solution might be:

=Time(Floor(Time,Time#(Time(vBinSize,'hh:mm'),'hh:mm')),'hh:mm')

Re: Reference DateTime for calculating occupancy

dafnis14 — Sat, 04 Feb 2017 06:12:48 GMT

Thank you!

Re: Reference DateTime for calculating occupancy

MarcoWedel — Sat, 04 Feb 2017 08:07:30 GMT

You're welcome

Regards

Marco