Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
if(not isNULL(JMS_DELIVERED_DATE_TIME) and not isNULL(FIRST_TOUCH_DATE_TIME),
Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}TRANSACTION_RECORD_KEY), 0)
giving me 0 values , but when I am going to use below sql:
select count( TRANSACTION_RECORD_KEY) as Transaction_Count
from edi_e2e_user.abc_e2e_tracking_record where
UNPACKAGED_DATE_TIME is not null and FIRST_TOUCH_DATE_TIME is not null and
CAST(FROM_TZ(CAST(UNPACKAGED_DATE_TIME AS TIMESTAMP), 'UTC') at time zone 'America/New_York' AS Timestamp) between
TO_TIMESTAMP('22-AUG-18 12.00.00.000000000 AM', 'DD-Mon-YY HH.MI.SS.FF9 AM') and
TO_TIMESTAMP('22-AUG-18 11.59.59.000000000 PM', 'DD-Mon-YY HH.MI.SS.FF9 AM');
I am getting 11178 rows, then please advise me how to use it in set expression, or if anything is wrong in my above code , please correct me
Hi,
Yor expression is not working for me when I am using it at KPI. But I am using the below expression and it works for me:
=Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"},
JMS_DELIVERED_DATE_TIME -={'=Len(Trim(JMS_DELIVERED_DATE_TIME))=0'}, FIRST_TOUCH_DATE_TIME -={'=Len(Trim(FIRST_TOUCH_DATE_TIME))=0'}>}TRANSACTION_RECORD_KEY)
Thanks,
Sandip
First try to split the expressions and see if you are getting any values. Regarding NULLs see if the below will help:
if(Len(Trim(JMS_DELIVERED_DATE_TIME)) >0 and Len(Trim(FIRST_TOUCH_DATE_TIME))>0,
Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}TRANSACTION_RECORD_KEY), 0)
Hi,
Yor expression is not working for me when I am using it at KPI. But I am using the below expression and it works for me:
=Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"},
JMS_DELIVERED_DATE_TIME -={'=Len(Trim(JMS_DELIVERED_DATE_TIME))=0'}, FIRST_TOUCH_DATE_TIME -={'=Len(Trim(FIRST_TOUCH_DATE_TIME))=0'}>}TRANSACTION_RECORD_KEY)
Thanks,
Sandip