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,

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

[sanketkhunte]

They both are in same format. 'YYYYMMDD' ?

[PrashantSangle]

Hi,

Post screen shot of it.

Regards

[sanketkhunte]

Please see below snap shot -:

With IF--ELSE getting correct result but with Set analysis its not showing except 0

[sunny_talwar]

Try this:

Count({<VENDOR_NAME = {"= PC_FIRST_ACTIVATED_DATE <= SC_LAST_CANCELLED_DATE"}>} PARTNER_ACCOUNT_ID)

[sanketkhunte]

Why did you add VENDOR_NAME Is it mandatory ?

[sunny_talwar]

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

[sanketkhunte]

Its still showing me zero result.

[PrashantSangle]

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

[sunny_talwar]

Do you have field which would make a combination of PC_FIRST_ACTIVATED_DATE, SC_LAST_CANCELLED_DATE unique? Some sort of ID?