topic date difference in QlikView

date difference

Wed, 26 May 2010 13:06:09 GMT

Good day,
I would like to have a date diff function in an expression. im getting a weird answer. say

date

(2010/10/10) -date(2010/10/11), im getting 1.82727272. does anyone know whats wrong here.

Regards.

)

date difference

boorgura — Wed, 26 May 2010 13:22:22 GMT

Try using:

date#('2010/10/11', 'YYYY/MM/DD') - Date#('2010/10/10', 'YYYY/MM/DD')

It gives the number of days difference.

You can also try the interval function.

Let me know if it works.

date difference

Wed, 26 May 2010 13:28:28 GMT

Dates are stored with days in the integer part, time in the fractional part of the number. The example calculatation you show has obviously the time in at least one of the values included. You can get rid of the time (fractional part) with floor, ceil or round, depending on what you want to achieve.

You can also just floor the result to get the number of days but it will return 0 for a part of a day difference.

Regards

Jürg

date difference

Thu, 27 May 2010 07:06:14 GMT

Hi Guys,

if i try to add an average function on this date format, it just seems not to work, Does anyone know whats wrong with this expression.

avg

(if((not isnull(DateCreated) and not isnull(DateIssued)),(date#(DateIssued,'YYYY/MM/DD')-date#(DateCreated,'YYYY/MM/DD')),null()))

Thank you,

date difference

Thu, 27 May 2010 07:15:43 GMT

thank you all guys, the floor function seem to have done the trick,

I dont understand why the date#(Date,'format') didnt work on running data. if i use type in the dates it works but not if i use a column name.

This is working

avg

(if((not isnull(DateCreated) and not isnull(DateIssued)),(floor(DateIssued)-floor(DateCreated)),null()))

Thanks guys.

date difference

Thu, 27 May 2010 07:34:29 GMT

use diff function in macro editor

function datediff2(d1,d2)
datediff2=datediff("D",d1,d2)
end function

And than call this function in qlikview editor

datediff2(Date1,Date2) it no of day between date1 and date2

Regards

Ashish