Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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;



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

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