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,
Set operators work between <> and <>
and + is work as Union
try below
Count({<SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={2},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}
>}PARTNER_ACCOUNT_ID)
Regards
Hi,
Try with Set Operators + in set analysis.
Try like
Count({<SC_ENABLED_STATE={1}> + <SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}>}ID)
Regards,
Thanks you. Let me try to keep you posted
I am facing issue while adding add clause -: Please see below expression in which i am facing issue -:
Count({<SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}+
SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}+
SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}+
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={2},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}
>}PARTNER_ACCOUNT_ID)
Hi,
Set operators work between <> and <>
and + is work as Union
try below
Count({<SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={2},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}
>}PARTNER_ACCOUNT_ID)
Regards
Thank you so much.
Thank you there.
Above expression is working fine.
However, Additionally i do have requirement to add one more clause of ACTIVATED_DATE with AND operator. I am trying below but its fetching error highlighted in RED -:
COUNT(DISTINCT
{<SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE ={2},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>,
ACTIVATED_DATE = {"< $(=DATE(v_EndDate,'MM/DD/YYYY'))"}
>}PARTNER_ACCOUNT_ID)
Hi,
Try this:
COUNT(DISTINCT
{<SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE ={2},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>,
ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}
>}PARTNER_ACCOUNT_ID)
You forgot an equal sign
Hi,
where do you want to add highlighted condition.
since you set analysis expression is enclosed within < and > brackets.
You can not write any condition outside < and >
Regards,
We can add this Activated DATE AND Condition at the beginning of expressoin.
I am trying the same, but fetching same error.
Using '*' operation for AND
Count( DISTINCT {<PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>*
<SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE-={1},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>
}
PARTNER_ACCOUNT_ID)
Can you provide me your expression that will helpful to me.