interval function not working..Please help

[ananyaghosh]

Hi,

I am using the below set expression and it is not working for me:-

interval(((sum({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}(interval(JMS_DELIVERED_DATE_TIME - FIRST_TOUCH_DATE_TIME, 'ss'))))/Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}TRANSACTION_RECORD_KEY))/86400, 'hh:mm:ss.fff')

I have checked the

sum({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}(interval(JMS_DELIVERED_DATE_TIME - FIRST_TOUCH_DATE_TIME, 'ss')))

and also

Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}TRANSACTION_RECORD_KEY)

and they are giving me correct result.

But when I am using the below set expression it is not working:

sum({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}(interval(JMS_DELIVERED_DATE_TIME - FIRST_TOUCH_DATE_TIME, 'ss')))

Please help how to solve this problem?

[sunny_talwar]

What is it giving? and what did you expect it would give? Would you be able to share a sample to check it out?

[jonathandienst]

Interval() is a formatting function only - so Interval(...., 'ss') does not convert the interval to seconds. You might need something like this

Interval(

  Sum({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}

  JMS_DELIVERED_DATE_TIME - FIRST_TOUCH_DATE_TIME)

  /

  Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}

  TRANSACTION_RECORD_KEY)

, 'hh:mm:ss.fff')

(I am assuming that JMS_DELIVERED_DATE_TIME and FIRST_TOUCH_DATE_TIME are proper numeric timestamp values)