topic Re: Heat map - Length of Stay for 24 hr in QlikView

Heat map - Length of Stay for 24 hr

regowins — Mon, 06 Jul 2015 23:09:14 GMT

Hi All,

For resource planning I have created a heat map showing the length of stay of a visiting person over a 24 hr period by day. For example if a person arrives at Mon 10 am and departs at Mon 2 pm I will get a flag of 1 for each hr from 10 am to 2 pm. The question I have is how do I show a visit in the same 24 hr chart for a person that visited on Mon at 11:45 pm and Departed the following day on Tue

at 4 am.

The attached qvw works great for all people that arrived and left in the same 24 HR period. but I need a way to account for people that left the following day in the same chart for resource planning.

Working correctly with a 24 hr period

Type	Arrival_Datetime	Arrival_Time	AHour	Depart_Datetime	Depart_Time	DHour	Occupancy_Hour	Visits
Visit	12/21/2011 1:40:01 AM	01:40:01	1	12/21/2011 6:39:00 AM	06:39:00	6	1	1
Visit	12/31/2011 1:42:50 AM	01:42:50	1	12/31/2011 2:40:00 AM	02:40:00	2	1	1

Day	HR	1	2	3	4	5	6
Wed		1	1	1	1	1	1
Sat		1	1	-	-	-	-

Need help with More than 24 hr period

1st visit I would like a flag in 21 - 23 hrs and next day 0 - 1 hrs ( the next day flag needs to show in the same chart)

2nd visit I would like a flag in 21 - 23 hrs and next day 0 hrs

Type	Arrival_Datetime	Arrival_Time	AHour	Depart_Datetime	Depart_Time	DHour	Occupancy_Hour	Visits
Visit	1/2/2012 9:25:20 PM	21:25:20	21	1/3/2012 1:07:00 AM	01:07:00	1	21	1
Visit	1/7/2012 9:02:02 PM	21:02:02	21	1/8/2012 12:47:00 AM	00:47:00	0	21	1

Day	HR	-
Mon		1
Sat		1

I have attached the Qvw

Thanks for the help

Re: Heat map - Length of Stay for 24 hr

crusader_ — Tue, 07 Jul 2015 08:01:11 GMT

Hello Ashwin,

Why don't you work with timestamps istead of working with time and dates separately?

If you change your IntervalMatch script a bit to work with timestamps the timeline would be interpreted as a single continuous entity and your problem will be resolved automatically.

Hope this helps you.

Regards,

Andrei Kaliahin

Re: Heat map - Length of Stay for 24 hr

regowins — Tue, 07 Jul 2015 14:58:07 GMT

Thanks, I am not sure how it would work with time-stamp. Can you give me a quick example. The other issue I had when with using time-stamp is if I you a time-stamp events of 12:00, 1:00, 2:00 and I have an interval from 12:10 - 2:17 I will only get a match on 1:00 and 2:00 but not 12 some reason since it falls outside the range.

Re: Heat map - Length of Stay for 24 hr

crusader_ — Wed, 08 Jul 2015 08:11:52 GMT

Hi,

Please find attached my example.

Read script and comments carefully there are some useful tips.

If you still have questions feel free to contact me.

Have a nice day.

Regards,

Andrei

Re: Heat map - Length of Stay for 24 hr

regowins — Sun, 12 Jul 2015 02:16:54 GMT

Andrei - Sorry of the late response, this was a PERFECT example!!! I had initially tried to do it with timestamp but I did not think of converting to number. I also did not think of using the calendar gen to create the intervals. Again, THANK YOU for taking the time to put this example together!

Regards,

-Ash