Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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

Re: Require hrs calculation

try

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

qlikview979
Valued Contributor

Re: Require hrs calculation

Hi Nisha,

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

Regards,

Mahesh

Not applicable

Re: Require hrs calculation

Dear Kushal,

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


It shows 0 hrs.


Please check the syntax.


Regards,


novolouy
New Contributor II

Re: Require hrs calculation

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
New Contributor III

Re: Require hrs calculation

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

Re: Require hrs calculation

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
New Contributor II

Re: Require hrs calculation

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
New Contributor III

Re: Require hrs calculation

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