topic Re: calculate (date-time) difference in QlikView

calculate (date-time) difference

Anonymous — Thu, 06 Nov 2014 06:08:23 GMT

I have 2 dates with time stamp of the format 'dd/mm/yyyy hh:mm:ss'. I need to calculate the difference between the 2 in seconds and then convert it back to days because in between the 2 dates, I may have bank holidays and weekends which need not be considered in the difference between the 2 dates. however, the final date time difference must still be in the format 'days hours minutes seconds..

I would like to know if this is possible in Qlikview. Kindly provide me with an example if it is possible

Many thanks

Hasnaa

Re: calculate (date-time) difference

jagan — Thu, 06 Nov 2014 06:31:48 GMT

Hi,

Check this Interval()

interval( A-B, 'D hh:mm' )

Example:

Interval(EndDate - StartDate, 'D') - Gives in days

Interval(EndDate - StartDate, 's') - Gives in seconds

From Qlikview Help file

Interval

interval(expression [ , format-code ])

The interval function formats the expression as a time interval according to the string given as a format-code. If the format code is omitted, the time format set in the operating system is used. Intervals may be formatted as a time, as days or as a combination of days, hours, minutes, seconds and fractions of seconds.

Examples:

The examples below assume the following operating system settings:

Short date format:	YY-MM-DD
Time format:	hh:mm:ss
Number decimal separator:	.

interval( A ) where A=0.375 returns:


String	09:00:00
Number	0.375

interval( A ) where A=1.375 returns:


String	33:00:00
Number	1.375

interval( A, 'D hh:mm' ) where A=1.375 returns:


String	1 09:00
Number	1.375

interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:


String	365 09:00
Number	365.375

Re: calculate (date-time) difference

Anonymous — Thu, 06 Nov 2014 07:14:41 GMT

For testing purposes, I have set 2 dates in the script as following:

set date1 = '06/11/2014 11:00:10';

set date2 = '07/11/2014 11:10:20';

In a text object, I have used the following expression:

=interval($(date2)-$(date1),'D')

It does not work, it displays null. I have also tried =interval((date2-date1),'D') but to no avail

Please advise

Thanks

Hasnaa

Re: calculate (date-time) difference

senpradip007 — Thu, 06 Nov 2014 07:27:24 GMT

Try this in script

LET date1 = Timestamp(Timestamp#('06/11/2014 11:00:10', 'DD/MM/YYYY hh:mm:ss'));

LET date2 = Timestamp(Timestamp#('07/11/2014 11:10:20', 'DD/MM/YYYY hh:mm:ss'));

and in expression

=interval((date2) - (date1), 'D')

Re: calculate (date-time) difference

ashfaq_haseeb — Thu, 06 Nov 2014 07:33:43 GMT

Hi,

Try like this

=interval(date(date#(date2,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')-date(date#(date1,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'D')

Regards

ASHFAQ

Re: calculate (date-time) difference

Anonymous — Thu, 06 Nov 2014 07:42:22 GMT

Hi,

You may also have a look on function "networkdays()". This function returns no of working days between monday to friday. You can even exclude holidays by listing them in function.

Thanks

Re: calculate (date-time) difference

Thu, 06 Nov 2014 07:59:19 GMT

Hi,

PFA attached as per Pradip Sen suggestion.

Best,

Robert

Re: calculate (date-time) difference

Anonymous — Thu, 06 Nov 2014 08:00:36 GMT

Thanks. It works

Re: calculate (date-time) difference

Anonymous — Thu, 06 Nov 2014 08:01:57 GMT

Thanks all for your help

Regards

Hasnaa

Re: calculate (date-time) difference

Anonymous — Thu, 06 Nov 2014 09:01:49 GMT

Instead of SET use LEt to evaluate the expression then it will work

Re: calculate (date-time) difference

MarcoWedel — Thu, 06 Nov 2014 11:23:05 GMT

Hi,

if your start and end dates are always working days, then one solution could be also:

tabDates:
LOAD *,
    Interval(Frac(DateTimeEnd)-Frac(DateTimeStart)-1+NetWorkDays, 'd hh:mm:ss') as WorkInterval;
LOAD *,
    NetWorkDays(DateTimeStart, DateTimeEnd) as NetWorkDays,
    Interval(DateTimeEnd-DateTimeStart, 'd hh:mm:ss') as Interval
Where WeekDay(DateTimeStart)<=4 and WeekDay(DateTimeEnd)<=4;
LOAD *,
    Timestamp(DateTimeStart+Rand()*20) as DateTimeEnd;
LOAD Timestamp(MakeDate(2014)+Rand()*200) as DateTimeStart
AutoGenerate 100;

hope this helps

regards

Marco