QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
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

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

allDatesAndTime:
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

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:

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

start,end
resident
testData;

Contributor III
Author

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.