Qlikview Set Analysis

[sanketkhunte]

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

[PrashantSangle]

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,

[sanketkhunte]

Thanks you. Let me try to keep you posted

[sanketkhunte]

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)

[PrashantSangle · Accepted solution]

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

[sanketkhunte]

Thank you so much.

[sanketkhunte]

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)

[Anonymous]

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

[PrashantSangle]

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,

[sanketkhunte]

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.