Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try
Hour(frac(max(GATE_OUT_EVENTDATE)) - frac(Min(EVENTDATE)))
Hi Nisha,
In your source having one Date field not two Date field .
Regards,
Mahesh
Dear Kushal,
Hour(frac(max(GATE_OUT_EVENTDATE)) - frac(Min(EVENTDATE))) does not work.
It shows 0 hrs.
Please check the syntax.
Regards,
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,
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.
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,
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)
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.