topic Re: Time / Days Calculation Expression in QlikView

Time / Days Calculation Expression

khanashique — Sun, 18 Mar 2018 11:48:52 GMT

Hi there,

How do i calculate durations in days,hours,mins (need to use time / date expression)

Example: Start Date and Time = 01 Jan 2018, 07:30 AM

End Date and Time = 02 Jan 2018, 10:00 PM

Difference comes: 1 Day, 14 Hours, 30 Mins.

I have used below expression:

TIMESTAMP([ENDDATE]-[STARTDATE],'HH:MM:SS') --> it shows calculation only in Time i want to see days too.

Thanks.

MAK

Re: Time / Days Calculation Expression

MarcoWedel — Sun, 18 Mar 2018 22:33:42 GMT

Interval([ENDDATE]-[STARTDATE],'d hh:mm:ss')

Re: Time / Days Calculation Expression

khanashique — Mon, 19 Mar 2018 05:01:49 GMT

Thanks for the reply, but the above expression gives difference only in days, no time is being calculated

Re: Time / Days Calculation Expression

MarcoWedel — Mon, 19 Mar 2018 21:45:21 GMT

The expression seems to work for me, i.e it gives difference not only in days, but time is being calculated also:

hope this helps

regards

Marco

Re: Time / Days Calculation Expression

khanashique — Tue, 20 Mar 2018 04:45:30 GMT

Thanks once again for your effort...

but i get same calculation even after i normally subtract end date and start date by changing the pattern in properties 'DD HH:MM:SS'

I need to see somthing like below mentioned

" 1 Day, 14 Hours, 30 Mins."

Thanks.

MAK.

Re: Time / Days Calculation Expression

MarcoWedel — Tue, 20 Mar 2018 23:07:21 GMT

maybe like this?

table1:
LOAD *,
    Dual(' '&Floor([ENDDATE]-[STARTDATE])&' Day, '&Hour([ENDDATE]-[STARTDATE])&' Hours, '&Minute([ENDDATE]-[STARTDATE])&' Mins.',[ENDDATE]-[STARTDATE]) as INTERVAL
INLINE [
    STARTDATE, ENDDATE
    "01 Jan 2018, 07:00 AM", "02 Jan 2018, 07:00 AM"
    "01 Jan 2018, 07:30 AM", "02 Jan 2018, 10:00 PM"
    "02 Jan 2018, 12:00 AM", "02 Jan 2018, 12:00 PM"
    "03 Jan 2018, 01:00 PM", "07 Jan 2018, 02:34 PM"
];

regards

Marco

Re: Time / Days Calculation Expression

khanashique — Wed, 21 Mar 2018 05:16:20 GMT

Hi Marco,

This is great, thanks a lot, it worked...

Can i ask little more help....

Example:

Working Day --> Monday to Friday

Week Off Day --> Saturday and Sunday

Shifts Hours --> 07:30 AM to 05:30 PM

So the time calculation should exclude the days and hours other than working day and shift hours.

Thanks,

MAK.