Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
What is it giving? and what did you expect it would give? Would you be able to share a sample to check it out?
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)