Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
sayadutt
Creator
Creator

Urgent: calculating working hours from 2 date fields

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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.

View solution in original post

11 Replies
MK_QSL
MVP
MVP

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

Anonymous
Not applicable

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
Creator
Creator
Author

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
Not applicable

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
Creator
Creator
Author

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
Not applicable

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
Creator
Creator
Author

Thanks a lot Michael. Its worked.

sayadutt
Creator
Creator
Author

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
Not applicable

See attached.

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