topic Re: Require hrs calculation in QlikView

Require hrs calculation

Fri, 27 May 2016 11:31:01 GMT

Hi,

I have two date field .

1. Get In Date Logic = Min(EVENTDATE)

2. Get Out Date = date(max(GATE_OUT_EVENTDATE),'DD-MM-YYYY')

I require total hours Calculation. i.e (Get Out Date - Get In Date) = Total hrs work

Please find the sample data of event date.

Kindly provide solution for it.

Regards

Re: Require hrs calculation

Kushal_Chawda — Fri, 27 May 2016 11:38:37 GMT

try

Hour(frac(max(GATE_OUT_EVENTDATE)) - frac(Min(EVENTDATE)))

Re: Require hrs calculation

qlikview979 — Fri, 27 May 2016 11:39:56 GMT

Hi Nisha,

In your source having one Date field not two Date field .

Regards,

Mahesh

Re: Require hrs calculation

Fri, 27 May 2016 12:19:45 GMT

Dear Kushal,

Hour(frac(max(GATE_OUT_EVENTDATE)) - frac(Min(EVENTDATE))) does not work.

It shows 0 hrs.

Please check the syntax.

Regards,

Re: Require hrs calculation

novolouy — Fri, 27 May 2016 13:04:15 GMT

Hi Nisha,

The first problem is the fact that in the Excel file you provided, you have different date formats, which it seems to be causing Qlikview to load the field as text not as a date.

So not taking the above issue into consideration and assuming I have a correct list of dates directly from the source table, here is how you can retrieve the amount of hours between two dates:

First, if we get the Min and Max date, and we subtract them directly, we will get the number of days between the two dates:

MAX(EVENTDATE) - MIN(EVENTDATE)

Asuming the Min = 1/31/2016 and Max = 5/26/2016 we get a difference of 116 days.

If we multiply those days by 24 we get the hours: 2784 (which I gues is what you are looking for.

There is a more direct way of doing this by using the INTERVAL expression:

INTERVAL(MAX(EVENTDATE) - MIN(EVENTDATE))

The Interval expression will give you the hours, minutes and seconds between the two dates. This works best when using a timestamp, since it will give you the difference in minues and seconds as well.

Please find attached the excel file I used and the example QVW.

Regards,

Re: Require hrs calculation

isaaclin — Fri, 27 May 2016 13:32:53 GMT

Hello Need to understand your question more, need more details.

I only see one column of data in your file. and the date format is not consistent too.

Re: Require hrs calculation

Mon, 30 May 2016 13:06:30 GMT

Hi,

For eg : Get Out Date = 29-05-2016 10.16.00 and Get In Date 28-05-2016 08.00.00

That means Get in date from 28th May 2016 8.00 AM to 29th May 2016 8.00 Am is 24 hrs and from 8.00am to 10.16.00 total is 34 hrs and 16min .

System should display 34 hrs and 16mins .

I hope you get my issue.

Interval(max(Eventdate)- Interval(min(eventdate)) does not give proper hours.

Please provide the solution on the above issue.

Regards,

Re: Require hrs calculation

novolouy — Mon, 30 May 2016 13:29:47 GMT

Hi Nisha,

In fact, the "INTERVAL" function is still what you need.

So assuming we have the following timestamps:

[GET IN DATE] =   5/28/2016 8:00:00 AM
[GET OUT DATE] =  5/29/2016 10:16:00 PM

Between those timestamps we get a difference of 38 hours and 16 minutes.

To get to that result we still use the INTERVAL function as shown below:

INTERVAL([GET OUT DATE] - [GET IN DATE], 'hh:mm:ss')

This gives us a result of:

38:16:00

Please find attached the example QVW showing the calculations. (Also attached the excel file I used for this example)

Re: Require hrs calculation

isaaclin — Mon, 30 May 2016 13:40:21 GMT

Hello, below is my test, i only kept two dates in your excel file for testing purpose.

you need to use your date times 24 before subtract

actually I got this idea from excel formula, I used this all the time in excel.

I tested in qlik, and it worked too.