calculate (date-time) difference

[Anonymous]

Hi

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

[jagan]

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

[Anonymous]

Hi

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

[senpradip007 · Accepted solution]

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')

[ashfaq_haseeb]

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

[Anonymous]

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

[#7]

Hi,

PFA attached as per Pradip Sen suggestion.

Best,

Robert

[Anonymous]

Thanks. It works

[Anonymous]

Thanks all for your help

Regards

Hasnaa

[Anonymous]

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