Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Require hrs calculation

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

8 Replies
Kushal_Chawda

try

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

qlikview979
Specialist
Specialist

Hi Nisha,

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

Regards,

Mahesh

Not applicable
Author

Dear Kushal,

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


It shows 0 hrs.


Please check the syntax.


Regards,


novolouy
Contributor III
Contributor III

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,

isaaclin
Contributor III
Contributor III

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.

Not applicable
Author

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,

novolouy
Contributor III
Contributor III

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)

isaaclin
Contributor III
Contributor III

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.

Capture1.JPGCapture.JPG