Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
How can i convert below expression into Set Analysis ?
COUNT
(
DISTINCT
IF(
(
SC_ENABLED_STATE = 1 or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE = 1) OR
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') <= DATE(SC_START_DATE,'MM/DD/YYYY') ) OR
(DATE( SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY')) or
(DATE(SC_LAST_CANCELLED_DATE,'MM/DD/YYYY') = '' AND SC_ENABLED_STATE <> 1 AND DATE(SC_MODIFY_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY') )
)
,ID))
Many Thanks
Sanket
Hi,
Is both field PC_FIRST_ACTIVATED_DATE and SC_LAST_CANCELED_DATE in same format???
take list box of both field and check it also if possible post screen shot of it so that we can see.
Regards
They both are in same format. 'YYYYMMDD' ?
Hi,
Post screen shot of it.
Regards
Please see below snap shot -:
With IF--ELSE getting correct result but with Set analysis its not showing except 0
Try this:
Count({<VENDOR_NAME = {"= PC_FIRST_ACTIVATED_DATE <= SC_LAST_CANCELLED_DATE"}>} PARTNER_ACCOUNT_ID)
Why did you add VENDOR_NAME Is it mandatory ?
I saw that the screenshot above has a vendor name as the dimension where you are checking the if condition for. So for each vendor name you are checking if PC_FIRST_ACTIVATED_DATE <= SC_LAST_CANCELLED_DATE
Its still showing me zero result.
Hi,
Your every screen shot and expressions tell different stories
some where you are writing expression like
(DATE(PC_FIRST_ACTIVATED_DATE,'MM/DD/YYYY') <= DATE((v_Max_SC_Start_Date),'MM/DD/YYYY'))
somewhere
(PC_FIRST_ACTIVATED_DATE) <= DATE((v_Max_SC_Start_Date),'MM/DD/YYYY'))
Somewhere comparing variable is different, Similarly Same with set analysis
somewhere you are formatting comparing field with date format, somewhere you are not doing.
For comparing date field, date format of both field must be same then only it will give you result.
Regards
Do you have field which would make a combination of PC_FIRST_ACTIVATED_DATE, SC_LAST_CANCELLED_DATE unique? Some sort of ID?