Urgent: calculating working hours from 2 date fields

[sayadutt]

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

[MK_QSL]

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

[Anonymous]

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.

[sayadutt]

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

[Anonymous · Accepted solution]

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.

[sayadutt]

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

[Anonymous]

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.

[sayadutt]

Thanks a lot Michael. Its worked.

[sayadutt]

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

[Anonymous]

See attached.

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