Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
// 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;
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
Hey.
Great example. But how do I get the right total. Now it seems to count the unique hours.
Thanks in advance, d