calculate the number of "working minutes"

[yacine_b]

Hi,

I have the following table:

Id	Begin_date	End_date
1	22/07/2010 10:00	22/07/2010 15:00
2	22/07/2010 07:00	23/07/2010 20:00
3	23/07/2010 12:00	26/07/2010 12:00
4	23/07/2010 19:00	25/07/2010 22:00
5	24/07/2010 16:00	26/07/2010 10:00

I want to have the following table in Qlikview:

Id	Begin_date	End_date	Working_Time_Stamp
1	22/07/2010 10:00	22/07/2010 15:00	300
2	22/07/2010 07:00	23/07/2010 20:00	1080
3	23/07/2010 12:00	26/07/2010 12:00	540
4	23/07/2010 19:00	25/07/2010 22:00	0
5	24/07/2010 16:00	26/07/2010 10:00	120

Working_Time_Stamp is the number of "working minutes" between Begin_date and End_date.

One "working minutes" is a minute which is in a work-day and work-hour; every Monday, Tuesday, Wednesday, Thursday, Friday from 09:00 to 18:00

Regards,

Yacine

[Bjorn_Wedbratt]

// Generating all dates and hours
// beginning 100 days back from now
// and 2000 hrs ahead

allDatesAndTime:
Load
today()-100+recno()/24 as time
autogenerate
20000;

// Getting the working hours
// in this case Mon to Fri
// between 7-17
// Use sum(workHour) in charts

Load
time,
hour(time) as hour,
if( weekday(time)<=4, // weekday sat=5 and weekday sun=6
if( hour(time)>=7 and hour(time)<=17, 1, 0)
) as workHour
resident
allDatesAndTime;

// Some events with start and end times
// Could be replaced with live data

testData:

Load * INLINE [

event, start, end

A, 2010-10-03 23:00:00, 2010-10-06 15:00:00

B, 2010-09-24 23:00:00, 2010-09-30 05:00:00

C, 2010-09-25 06:00:00, 2010-09-29 18:00:00

];

// Using intervalmatch to match values from
// the timetable with start and end values in
// the datatable

Intervalmatch(time) Load
start,end
resident
testData;

[yacine_b]

Thanks Bjorn for your reply,

However, there's a little difference between the solution needed and yours:

1) I need to calculate the "working minutes" not the working hours, I mean, I need to have the number on minutes between the two dates (not the number of hours), so I can have the following table:

A	2010-10-03 23:11	2010-10-06 15:20
B	2010-09-24 23:56	2010-09-30 05:16
C	2010-09-25 06:00	2010-09-29 18:03

2) I need to have the result in a field, not to calculate it in a chart

Regards,

Yacine

[#4]

Hey.

Great example. But how do I get the right total. Now it seems to count the unique hours.

Thanks in advance, d