Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

calculate the number of "working minutes"

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

3 Replies
Highlighted
Support
Support

// 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;



Highlighted
Contributor III
Contributor III

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

Highlighted
Not applicable

Hey.

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

Thanks in advance, d

workinghour.png