topic Re: Urgent: calculating working hours from 2 date fields in QlikView

Urgent: calculating working hours from 2 date fields

sayadutt — Fri, 03 Apr 2015 11:04:17 GMT

Hi All,

I have 2 date fields [Issue Open Time] and [Issue Closed Time] which are of the following format: 1/27/2015 12:00 AM

My Requirement is:

To calculate the hour difference between the two fields based on working hours.

Working hours: 10 AM to 7 PM

Lets say 1 ticket is opened at 11 AM and closed at 2 PM same day. The difference will be 3 hours (simple one)

But lets say, another ticket was opened on 1/27/2015 5 PM and was closed next day 1/28/2015 11.30 AM.

So the period for which the ticket was opened would be:

2 hours from 1/27 ( 5 PM to 7 PM)

and 1.30 hours from 1/28 (10 AM to 11.30 AM) = TOTAL 3.30 hours

Can you please let me know how I can get this value (duration for which the ticket was opened)

Thanks

Re: Urgent: calculating working hours from 2 date fields

MK_QSL — Fri, 03 Apr 2015 11:16:58 GMT

Provide sample data or dummy lines.. would work on this for you....

Re: Urgent: calculating working hours from 2 date fields

Anonymous — Fri, 03 Apr 2015 11:36:44 GMT

There was a similar question last year, and my response is attached. The only difference that I have working ours from 8 am to 6 pm, but you can change it, see variables in the script on lines 12 and 13.

Re: Urgent: calculating working hours from 2 date fields

sayadutt — Fri, 03 Apr 2015 12:54:46 GMT

Hi Michael,

Yes, this is exactly what I am looking for.

However, the only difference we have is in the time and date format.

My Format:

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

Your Format:

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='M/D/YYYY hh:mm:ss';

Can you please send me the qvw in my format please. I am loading the data from sharepoint and have to follow this.

Example of my format: 1/13/2015 4:15 PM

Also, the workhour field should show me the difference in hours only.

can you please help. I am pretty new in qlikview

Re: Urgent: calculating working hours from 2 date fields

Anonymous — Fri, 03 Apr 2015 13:38:23 GMT

See attached. In the script, I only changed the format settings and the sample data in the inline load

To convert to hours only,- see additional expression in the table.

Edit: the attachment was wrong - fixed.

Re: Urgent: calculating working hours from 2 date fields

sayadutt — Fri, 03 Apr 2015 14:08:19 GMT

Hi Michael,

I don't need the seconds in the time field.

My format is 10/12/2013 9:00 AM while yours is 10/12/2013 9:00:00 AM

Do i need to make any changes in the below code: to remove the seconds part?(line 29 to 35)

if(WeekDay(DateInitial)<5,

rangemin(rangemax(DateInitial,(floor(DateInitial) + $(vStartTime)/24)),floor(DateInitial)+$(vEndTime)/24) ,

floor(DateInitial)+(7-WeekDay(DateInitial))+$(vStartTime)/24 ) as Start,

if(WeekDay(DateEnd)<5,

rangemax( rangemin(DateEnd,(floor(DateEnd) + $(vEndTime)/24)),floor(DateEnd)+$(vStartTime)/24),

floor(DateEnd)+(4-WeekDay(DateEnd))+$(vEndTime)/24 ) as End

Re: Urgent: calculating working hours from 2 date fields

Anonymous — Fri, 03 Apr 2015 14:16:11 GMT

If you don't need seconds, it is enough to modify "Number" format in the chart properties for your expression. Change hh:mm: ss to hh:mm

No need to modify this part of the script, it returns End and Start in a numeric format anyway.

Re: Urgent: calculating working hours from 2 date fields

sayadutt — Fri, 03 Apr 2015 14:22:28 GMT

Thanks a lot Michael. Its worked.

Re: Urgent: calculating working hours from 2 date fields

sayadutt — Fri, 10 Apr 2015 12:47:05 GMT

Hi Michael,

Your solution is perfect when we load inline data and have Key as the 1st column.

However, I am loading data which has the following columns. Can you please modify the attached sample.

LOAD * Inline [

Project, Release, DateInitial, DateEnd, Hours

1, b, 10/12/2013 9:00:00 AM, 10/14/2013 9:00:00 AM, 1

1, b, 10/11/2013 5:00:00 PM, 10/12/2013 8:40:25 AM, 1

2, c, 10/11/2013 8:41:03 AM, 10/14/2013 8:43:11 AM, 1

2, c, 10/14/2013 8:39:09 AM, 10/14/2013 6:59:11 PM, 1

1, c, 10/14/2013 8:39:09 AM, 10/14/2013 10:39:11 AM, 1

1, b, 10/14/2013 8:39:09 AM, 10/15/2013 8:39:11 AM, 1

];

Currently the sample is showing 1,c -- as thats the unique row wrt Project release.

How can we display for all 6 rows?

Thanks

Re: Urgent: calculating working hours from 2 date fields

Anonymous — Fri, 10 Apr 2015 13:34:47 GMT

See attached.

I added a unique identifier (line 16 in the script), used it for join (line 27), and added in the table dimension.

Re: Urgent: calculating working hours from 2 date fields

sayadutt — Sat, 11 Apr 2015 07:33:26 GMT

Thanks Michael, it works.

For 1 more updation I need for help.

My DateEnd can be blank. (when the ticket is still in open state). For this scenario we need the count the downtime based on system reload time. From the time ticket is opened till now (when reloaded).

How can we do that.

Sample data:

LOAD * Inline [

Project, Release, DateInitial, DateEnd

1, b, 10/12/2013 9:00:00 AM, 10/14/2013 9:00:00 AM

1, b, 10/11/2013 5:00:00 PM, 10/12/2013 8:40:25 AM

2, c, 10/11/2013 8:41:03 AM, 10/14/2013 8:43:11 AM

2, c, 10/14/2013 8:39:09 AM, 10/14/2013 6:59:11 PM

1, c, 10/14/2013 8:39:09 AM, 10/14/2013 10:39:11 AM

1, b, 10/14/2013 8:39:09 AM,

];

last row ticket is still open

Re: Urgent: calculating working hours from 2 date fields

Anonymous — Sat, 11 Apr 2015 13:37:36 GMT

You need to replace the missing DateEnd with the current time using function now(). In case of inline load, you can do it like this:

Data:
LOAD
RowNo() as Key,
Project,
Release,
DateInitial,
timestamp(if(len(trim(DateEnd))=0, now(), DateEnd)) as DateEnd
Inline [
Project, Release, DateInitial, DateEnd
1, b,    10/12/2013 9:00:00 AM, 10/14/2013 9:00:00 AM
1, b, 10/11/2013 5:00:00 PM, 10/12/2013 8:40:25 AM
2, c, 10/11/2013 8:41:03 AM, 10/14/2013 8:43:11 AM
2, c, 10/14/2013 8:39:09 AM, 10/14/2013 6:59:11 PM
1, c,    10/14/2013 8:39:09 AM, 10/14/2013 10:39:11 AM
1, b,    10/14/2013 8:39:09 AM, 10/15/2013 8:39:11 AM
3, a, 4/10/2015 12:05:00 PM
];

...

More important, in your situation the time intervals could be longer than one working day, which was not supported by the expressions in the chart. I've made changes in the expressions to support this - see attached.
And, a minor change - you prevously asked about the "14" - to make clearer I repalced with a variable that holds the amount of non-working hours per day: WHperDay.