topic Calculate hrs and mins in QlikView

Calculate hrs and mins

Fri, 25 May 2012 12:43:33 GMT

Data set is given below:

Close Datetime Start Datetime Total HH:MM Req HH:MM

6/23/2011 8:46:34 AM 5/22/2011 6:48:11 PM 757.97 176.00

7/25/2011 11:16:45 AM 5/23/2011 8:07:36 AM 1515.15 362.27

12/26/2011 7:48:56 PM 5/23/2011 9:30:55 AM 5218.30 1247.50

Requriment result is in Red

Condition is hh:mm calculate base 9:00am to 5:00pm working hrs and min,

not includes saturday, sunday and other hrs and min.

for example: 5/21/2012 4:00pm is start time and 5/22/2012 10:00am is close time, so result is 2:00hrs.

5/21/2012 3:45pm is start time and 5/22/2012 10:30am is close time, so result is 2:45hrs.

5/18/2012 3:00pm is start time and 5/21/2012 11:00am is close time, so result is 4:00hrs. not used sat & sun

Calculate hrs and mins

john_duffy — Fri, 25 May 2012 13:38:31 GMT

Hello.

Here's the basic logic I use to accomplish this type of task:

Set a variable to calculate the number of business days between the two dates:

eg. Set vNetBusinessDays = networkingdays(Close Date,Start Date)

Note: You can also include holidays in the networkingdays function if you want to ignore them.

Set two variables for start and end working times:

eg. Let vStartTime = frac(Maketime(9))

Let vEndTime = frac(Maketime(17))

Set a variable for the number of hours in a working day:

eg. Set vEightHours = 0.333333333333 // interval (8/24)

Calculate the hours and minutes between the two Dates/Times:

If Start Date = Close Date --> interval(Close Date Time - Start Date Time)

If Start Date < Close Date --> Interval ($(vEndTime - frac(Start Date Time)) // hrs/mins between Start and 5:00 pm

+ Interval(frac(Close Date Time) - $(vStartTime)) // hrs/mins between 9:00 am and End

+ (vNetBusinessDays - 2) * $(vEightHours) // number of full days between the

Start and End

On your chart, set the Number Format Setting to Time (hh:mm).

The above code has not been fully tested. It should give you an idea for one method of calculating the hours/minutes between two dates.

Hope this helps,

John.

Re: Calculate hrs and mins

Jason_Michaelides — Fri, 25 May 2012 15:39:58 GMT

You need to use NetworkDays plus more logic for the partial days....

(NetWorkDays([Start DateTime],[Close DateTime]) //FULL working days between two dates, inclusive

- IF(Frac([Close DateTime])<Time#('17:00','hh:mm'),1,0) //if close day isn't complete subtract 1 from NetWork days

- IF(Frac([Start DateTime])>Time#('09:00','hh:mm'),1,0) //if start day isn't complete subtract 1 from NetWork days

)*8 //multiply by 8 hours per day. We now have the number of hours for all the FULL days between the dates

+ ((Time#('17:00','hh:mm') - Frac([Start DateTime]))*(1/24)) //add on the time the ticket was open on the first day (result is a fraction of a day so multiply by 1/24 to get hours)

+ ((Time#('17:00','hh:mm') - Frac([Close DateTime]))*(1/24)) //add on the time the ticket was open on the last day (result is a fraction of a day so multiply by 1/24 to get hours)

AS WorkHours

See attached,

Hope this helps,

Jason

Re: Calculate hrs and mins

Jason_Michaelides — Fri, 25 May 2012 15:46:53 GMT

Actually - this is almost right but there's a slight glitch somewhere...I'll find it

Re: Calculate hrs and mins

Jason_Michaelides — Fri, 25 May 2012 16:13:13 GMT

Here's the correct one (I believe):

(NetWorkDays([Start DateTime],[Close DateTime]) //FULL working days between two dates, inclusive

- IF(Frac([Close DateTime])<Time#('17:00','hh:mm'),1,0) //if close day isn't complete subtract 1 from NetWork days

- IF(Frac([Start DateTime])>Time#('09:00','hh:mm'),1,0) //if start day isn't complete subtract 1 from NetWork days

)*8 //multiply by 8 hours per day. We now have the number of hours for all the FULL days between the dates

+ RangeMax(((Time#('17:00','hh:mm') - Frac([Start DateTime]))*24),0) //add on the time the ticket was open on the first day (result is a fraction of a day so multiply by 24 to get hours). RangeMin() as if the start time is after 5pm this will be negative.

+ RangeMax(((Frac([Close DateTime]) - Time#('09:00','hh:mm'))*24),0) //add on the time the ticket was open on the last day (result is a fraction of a day so multiply by 24 to get hours). RangeMin() as if the end time is before 9am this will be negative.

AS WorkHours

See attached,

Jason

Re: Calculate hrs and mins

Fri, 25 May 2012 16:40:18 GMT

Shouldn't your 176 hour answer be 184 hours.

5/22 is sunday No Hours

5/23 to 5/27 40 Hrs

5/30 to 6/3 40 hrs

6/6 tp 6/10 40 hrs

6/13 to 6/17 40 hrs

6/20 to 6/22 24 hrs

6/23 -closed befor 9:00 - no Hours

total is 184?? What am I missing?

Stephen

Re: Calculate hrs and mins

Jason_Michaelides — Fri, 25 May 2012 16:59:17 GMT

Not sure if that question was aimed at me, Stephen but you have certainly highlighted an error in my script! Where I am adding and subtracting from NetworkDays() I should be performing a check and only do it if a weekday!

Will fix later...

Re: Calculate hrs and mins

Fri, 25 May 2012 23:59:09 GMT

Attached is a solution.

I first calculate NetWorkingDays Hours without the Start Day or the Closing Day.

Then I calculate the Start Day and Closing day time using the variables for 9:00 AM and 5:00PM. I also test to see if the Start Date or Closing Date is a Sat or Sun.

Then I add the sum of the Start Day and Closing Day hours plus any Start Day plus Closing Day minutes that round up to 1 hour.

I then calculate the remaining minutes.

I can see that I have a problem if a Start Date and Close Date are on the same date (will have to work on that).

I have left a lot of mid point calculations in the example.

HTH,

Stephen

Calculate hrs and mins

Sat, 26 May 2012 07:16:42 GMT

Thanks to all to share your knowledge for my requriment, i will check today and let you know if i have any issue

Regards,

Zain.

Re: Calculate hrs and mins

Tue, 29 May 2012 19:09:46 GMT

I have attached the final calculations which now take into account a start date and close date being the same day.

I have also add some notes about that each part of the scrip it doing.

Stephen

Re: Calculate hrs and mins

Mon, 04 Jun 2012 09:12:52 GMT

Thanks Sjcharies,

your solution is working fine,

now i am working on holidays within working days, for example 1st May 2012 or festivel holidays etc. i have year holidays list and i am trying to Incorporate in your script, if you have idea regarding this please share with me.

Regards,

Zain.

Re: Calculate hrs and mins

Tue, 05 Jun 2012 15:52:05 GMT

The syntax for networkdays is:

networkdays(

start_date, end_date {, holiday} )

Note that you cna add the holiday dates after the start date and the end date. You can add as many holiday dates as you need. So you would have to modify my script where every the is a networkdays statement

For example:

NetWorkDays

(StartDateTime,CloseDateTime,'5/1/2012'

)

Stephen