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

[sunny_talwar]

The way Max pointed out, that isn't working for you still even after changing the variable in your if statement?

[sanketkhunte]

Its not working.

[sanketkhunte]

Can I send you IF---ELSE expression again with v_EndDate variable ?

[sunny_talwar]

Not working is not very helpful? Is it giving you error the results aren't matching? What exactly is the problem? Can you share a sample with your expected output?

[sanketkhunte]

Hi Sunny,

Here i am sending you snapshot where i am getting '0' value while using set analysis expression and getting correct result with IF---ELSE  statement.

[sanketkhunte]

Hope this is helpful. Please let me know in case anything that i can provide on this.

Many Thanks

Sanket

[PrashantSangle]

Hi,

Just cross check that the date format of

field : PC_FIRST_ACTIVATED_DATE and

Variable : v_EndDate

or which ever you are comparing both need to be in same format .

Regards

[sunny_talwar]

Here, give this a try:

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)

Parts in red are the new additions.

One this to check is that PC_FIRST_ACTIVATED_DATE is in this format -> MM/DD/YYYY. If it is not then change date format in the red part to match with whatever format PC_FIRST_ACTIVATED_DATE is. To check you can create a list box and see what is the format it shows up with.

HTH

Best,

Sunny

[sanketkhunte]

Many Thanks Sunny.

[sanketkhunte]

Hi Sunny and team,

I don't know why getting '0' result while comparing 2 dates in set analsyis. I am using below expression

COUNT({< PC_FIRST_ACTIVATED_DATE = {"<=$(=Date(SC_LAST_CANCELLED_DATE,'YYYYMMDD')"} >} PARTNER_ACCOUNT_ID)

Please help on this.

Many Thanks.

Sanket