topic Number of customers online each hour of the day in QlikView

Number of customers online each hour of the day

dirk_konings — Fri, 12 Mar 2010 16:14:35 GMT

HI,

I have this data to read in QV:

Customer	Date	From	To
A	02/01/2010	6:30	8:30
B	02/01/2010	7:00	8:00
C	02/01/2010	7:00	7:30
D	02/01/2010	8:00	10:30
E	02/01/2010	8:00	8:15
F	02/01/2010	8:00	9:15
G	02/01/2010	10:00	12:00

and i want to have a chart like this :

From - To	# Customers
00:00 - 00:59	0
01:00 - 01:59	0
02:00 - 02:59	0
03:00 - 03:59	0
04:00 - 04:59	0
05:00 - 05:59	0
06:00 - 06:59	0,5
07:00 - 07:59	2,5
08:00 - 08:59	2,75
09:00 - 09:59	1,25
10:00 - 10:59	1,5
11:00 - 11:59	1
12:00 - 12:59	0

where each customer that is online a whole hour = 1

half hour = 0,5

What to do in the script for this ?? Is there a easy way ??

Thanks Dirk.

Number of customers online each hour of the day

Fri, 12 Mar 2010 16:36:58 GMT

Dirk,

take a look at 'intervalmatch' which should give you some ideas.

Regards,

Gordon

Number of customers online each hour of the day

dirk_konings — Sat, 13 Mar 2010 16:58:07 GMT

Gordon,

Don't think this will work. Intervalmatch is for 1 value between a min and max value.

In this case, i have to split every time into hour parts.

Dirk

Number of customers online each hour of the day

Oleg_Troyansky — Sun, 14 Mar 2010 03:11:39 GMT

Dirk,

if your data volume allows, you can generate hourly rows for each full or partial hour when the customer was active (based on a From-To range), and calculate the portion of the hour when the customer was online. See attached example.

best regards,

Re: Number of customers online each hour of the day

Mon, 24 Sep 2012 16:11:09 GMT

Oleg

I used your example to split the time into the minutes between each hour segment as such

Stationary_time_split:

load

RowID,

hour(time(HourStart, 'hh:mm')) as HourID,

time(HourStart, 'hh:mm') as HourStart,

time(HourEnd, 'hh:mm') as HourEnd,

num#(interval(time(rangemin([Vacated Time], HourEnd), 'hh:mm') - time(rangemax([Arrived Time], HourStart), 'hh:mm') , 'mm') ) as ActiveTime

;

load

floor([Arrived Time], 1/24) + (Iterno() - 1)/24 as HourStart,

floor([Arrived Time], 1/24) + (Iterno())/24 as HourEnd

resident Bay_Sensor_activity

while

floor([Arrived Time], 1/24) + Iterno()/24 <= ceil([Vacated Time], 1/24) + 0.02 //

;

What i am struggling with though is if the TO date is different to the FROM date, i.e the time period extends past midnight into the following day. I'm trying to amend the above to take account of the day and get it to generate the time through to the following day - however i'm struggling. Any help on this would be very much appreciated

Thanks

Lewis