topic Re: Work minutes in QlikView

Work minutes

Anonymous — Mon, 22 Dec 2014 02:00:38 GMT

Hi, I want to find the the WORK-MINUTES between 2 dates(DT1 and DT2) excluding weekends(other holidays not required). I have work hours from 1 AM to 10 PM(22:00)(we have teams across multiple geographies, hence extended work hours).

There are many posts however I was not able to achieve what I want.

The best I have come across is the below but I dont know what modification is needed for my requirement:

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)

// 10 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0)

// working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10))

// correct for first equals last day

)

Please advise.

Thanks!!!!

Sam

Re: Work minutes

Mon, 22 Dec 2014 02:13:01 GMT

Attach your app.

Regards,

Navdeep

Re: Work minutes

Anonymous — Mon, 22 Dec 2014 02:25:04 GMT

Hi Navdeep, The code I have copied is not from my app but a post earlier published in the Qlik community-

Working hours only

I tried to modify(as below) but it didn't work as expected -

Interval(

rangesum(

NetWorkDays(Created+1,[ACK Date]-1) * MakeTime(20)
// 20 hours per workday, for all day inbetween the period, excluding bounderies
,if(NetWorkDays([ACK Date],[ACK Date]),Rangemin(rangemax(frac([ACK Date]),maketime(1)),maketime(22))-Rangemax(rangemin(frac([ACK Date]),maketime(1)),maketime(1)),0)
// working hours last day
,if(NetWorkDays(Created,Created),Rangemin(rangemax(frac(Created),maketime(22)),maketime(22))-Rangemax(rangemin(frac(Created),maketime(22)),maketime(1)),0) // working hours first day
,if(NetWorkDays(Created,Created) and floor(Created)=floor([ACK Date]),-MakeTime(20))
// correct for first equals last day
)
)
as RespHrs

(Note: DT1 is Created and ACK Date i DT2)

Re: Work minutes

SunilChauhan — Mon, 22 Dec 2014 03:00:10 GMT

networkdays (start_date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

bolds are holidays, you assign it to variables .

netwok days excludes holidays

Re: Work minutes

Mon, 22 Dec 2014 03:23:59 GMT

The code which you have posted is useful, if you want to calculate working hours between 2 dates along with time stamp, and when you are using networkdays(), it is removing weekends.

If you can add your data, may be I can help you.

Regards,

Navdeep

Re: Work minutes

Anonymous — Mon, 22 Dec 2014 03:47:59 GMT

attaching the excel. my source is an excel file.

As mentioned earlier, I want to find working minute difference between these 2 dates. Also, I should exclude weekends from my calculation.

Work hours are M-F(1 AM to 10PM)

Note: Even 24 hrs of work hours(M-F) is okay with me. I just want to exclude weekends and find the minutes difference between the dates.

Re: Work minutes

SunilChauhan — Mon, 22 Dec 2014 04:15:45 GMT

network days()/60*60

Re: Work minutes

Anonymous — Mon, 22 Dec 2014 04:29:43 GMT

This doesn't give me the correct result. I had tried this earlier.

For dates which fall on same day, NetWork days returns 0 (so when we multiple anything we get 0). For same days, also, I need the difference in minutes.

eg:

Created - 12/22/2014 23:20:00

Ack Date - 12/22/2014 23:24:00

Should return 4 minutes (difference).

-------------

However, Interval([ACK Date] - Created) work perfectly but doesnt take the weekend into account.

Re: Work minutes

SunilChauhan — Mon, 22 Dec 2014 04:40:03 GMT

could you please attched sample we could give a try

Re: Work minutes

aveeeeeee7en — Mon, 22 Dec 2014 06:30:17 GMT

I've referred this post:

http://community.qlik.com/message/252929#252929

Try this Script:

Set TimestampFormat = 'M/D/YY hh:mm TT';
Set vHol = '41130,41140'; // Add your Holidays here

INPUT:
LOAD
Recno() as ID,
Created AS DT1,
[ACK Date] AS DT2

FROM
Dates.xlsx
(ooxml, embedded labels, table is Sheet1);

TMP:
LOAD ID,
daystart(DT1)+iterno()-1 as Date,
if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(13)),maketime(22)), maketime(13)) as Start,
if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(13),rangemin(frac(DT2),maketime(22))),Maketime(22)) as End

Resident INPUT
while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD
ID,
interval(sum(End-Start)) as Duration,
interval(sum(End-Start),'hh') as [Working Hours],
interval(sum(End-Start),'mm') as [Working Minutes]
Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by ID;
drop table TMP;

Note: Add Holidays in variable 'vHol'

The Final Result:

Hope that helps.

Also, see the Attachment.

Regards

Av7eN

Re: Work minutes

Tue, 23 Dec 2014 12:30:09 GMT

Hi,

PFA app, which excludes weekends and only considers the working time (1:00 am - 10:00 pm) in mins.

Hope this helps.

Regards,

Amay

Re: Work minutes

Anonymous — Tue, 30 Dec 2014 21:20:27 GMT

Thanks all for your help!

Sorry I couldnt respond earlier. I tested the code and it worked perfectly!

Thanks again!!!

Re: Work minutes

Anonymous — Tue, 30 Dec 2014 21:56:34 GMT

One follow up question:

I have multiple dates:

DT1, DT2

DT3, DT4

DT5,DT6

I want to find the work minutes between these dates. So can we create a subroutine and pass on the 2 dates and the ID field, and this should return the work minutes between those 2 dates for that ID?

Thanks