Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
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