topic Re: Qlikview Set Analysis in QlikView

Qlikview Set Analysis

sanketkhunte — Tue, 02 Feb 2016 10:54:56 GMT

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

Re: Qlikview Set Analysis

PrashantSangle — Tue, 02 Feb 2016 11:00:49 GMT

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,

Re: Qlikview Set Analysis

sanketkhunte — Tue, 02 Feb 2016 11:05:01 GMT

Thanks you. Let me try to keep you posted

Re: Qlikview Set Analysis

sanketkhunte — Tue, 02 Feb 2016 11:26:49 GMT

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)

Re: Qlikview Set Analysis

PrashantSangle — Tue, 02 Feb 2016 11:35:32 GMT

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

Re: Qlikview Set Analysis

sanketkhunte — Tue, 02 Feb 2016 11:49:18 GMT

Thank you so much.

Re: Qlikview Set Analysis

sanketkhunte — Wed, 03 Feb 2016 07:00:23 GMT

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)

Re: Qlikview Set Analysis

Anonymous — Wed, 03 Feb 2016 07:03:58 GMT

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

Re: Qlikview Set Analysis

PrashantSangle — Wed, 03 Feb 2016 07:59:57 GMT

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,

Re: Qlikview Set Analysis

sanketkhunte — Wed, 03 Feb 2016 08:26:37 GMT

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.

Re: Qlikview Set Analysis

sanketkhunte — Wed, 03 Feb 2016 08:33:43 GMT

Please see below expression with IF-ELSE which is working. Need to convert this into SET ANALYSIS syntax.

COUNT
(
DISTINCT
IF(

(DATE(PC_FIRST_ACTIVATED_DATE,'MM/DD/YYYY') <= DATE((v_Max_SC_Start_Date),'MM/DD/YYYY')) AND
(
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 ANDDATE(SC_MODIFY_DATE,'MM/DD/YYYY') >= DATE(v_EndDate,'MM/DD/YYYY') )
)

,ID))

Re: Qlikview Set Analysis

PrashantSangle — Wed, 03 Feb 2016 09:33:00 GMT

Hi,

try including And condition expression in every set of Or condition

try below,

Count( DISTINCT {
<SC_ENABLED_STATE={1},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE-={1},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"},PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>
}
PARTNER_ACCOUNT_ID)

Regards

Re: Qlikview Set Analysis

sanketkhunte — Wed, 03 Feb 2016 10:06:00 GMT

It is fetching 0 value. Where as IF -- ELSE Statement fetching 291 value which is correct.

Can we have other option to add AND operator.

Many Thanks

Sanket

Re: Qlikview Set Analysis

PrashantSangle — Wed, 03 Feb 2016 10:17:34 GMT

Hi,

might be other experts can give better solutions for this.

Dear All,

swuehl‌, jagan‌, tresesco‌, sunindia‌

Please suggest.

Regards,

Re: Qlikview Set Analysis

sunny_talwar — Wed, 03 Feb 2016 10:39:18 GMT

May be the variable is different:

Count( DISTINCT {
<SC_ENABLED_STATE={1}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''},SC_ENABLED_STATE={1}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {"<=$(=DATE(SC_START_DATE,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}> +
<SC_LAST_CANCELLED_DATE={''}, SC_ENABLED_STATE-={1}, SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}, PC_FIRST_ACTIVATED_DATE = {"<=$(=DATE(v_Max_SC_Start_Date,'MM/DD/YYYY'))"}>} PARTNER_ACCOUNT_ID)

Re: Qlikview Set Analysis

sanketkhunte — Wed, 03 Feb 2016 11:00:05 GMT

Thanks Sunny.

Variable is v_EndDate not v_Max_SC_Start_Date.

Many Thanks

Sanket

Re: Qlikview Set Analysis

sunny_talwar — Wed, 03 Feb 2016 11:04:08 GMT

But the if statement you showed above was comparing it again v_Max_SC_Start_Date

Re: Qlikview Set Analysis

sanketkhunte — Wed, 03 Feb 2016 11:06:11 GMT

Thanks for pointing out this but we need to use v_EndDate

Sorry for the inconvenience cause !!

Many Thanks

Sanket

Re: Qlikview Set Analysis

sunny_talwar — Wed, 03 Feb 2016 11:06:56 GMT

Is the issue resolved then?

Re: Qlikview Set Analysis

sanketkhunte — Wed, 03 Feb 2016 11:09:12 GMT

How can we add PC_ACTIVATED_DATE as AND clause in below set expression

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-={1},SC_MODIFY_DATE = {">=$(=DATE(v_EndDate,'MM/DD/YYYY'))"}>
}