topic Exclusion in Set Analysis in QlikView

Exclusion in Set Analysis

miikkaqlick — Tue, 06 Mar 2012 09:59:17 GMT

Hi All!

I'm making application that shows how often customers have bought things. I'll simplify it a bit to this. I need to groups, first is customers who have bought things in rolling 12 and second group is people who have bought things before this.

I'm making this with set analysis and with QV9 sr5.

Expression 1

Count({

(

$<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

)

}

Distinct Customer)

This gives me all customers who have bought things in selected rolling 12 period. Second expression is the problem:

Expression 2

Count({

(

$<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

)

(

$<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

)

}

Distinct Customer)

This gives me also customers who have bought things in rolling period. So I have customers that fits to both expressions. If I undestand second expression correctly then QV calculates 1-1=1. First part of expression returns 1 and second part of expression returns also 1.

I have various versions of this:

Version2:

Count({

(

$< Customer =

P({$<Product={'Things'}, InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}>})>

)

(

$<Customer =

E({$<InvoicingDate={">$(=v_Passive12)"}>})>

)

}

Distinct Customer)

If I take version 2 apart then first part of expression returns 1 and second part of expression returns 0, but for some reason 1 * 0 = 1. I have tried also - and + operators in this.

Any ideas?

Exclusion in Set Analysis

CELAMBARASAN — Tue, 06 Mar 2012 10:11:24 GMT

Hi,

This is the meaning of the operators.

+ Union. This binary operation returns a set consisting of the records that belong to any of the two set operands.

- Exclusion. This binary operation returns a set of the records that belong to the first but not the other of the two set operands. Also, when used as a unary operator, it returns the complement set.

* Intersection. This binary operation returns a set consisting of the records that belong to both of the two set operands.

/ Symmetric difference (XOR). This binary operation returns a set consisting of the records that belong to either, but not both of the two set operands.

Example

Set1={1,2,3,4}, Set2={3,4,5,6}

Set1+Set2={1,2,3,4,5,6}

Set1-Set2={1,2}

Set1*Set2={3,4}

Set1/Set2={1,2,5,6}

Like this way you understood?

Celambarasan

Exclusion in Set Analysis

miikkaqlick — Tue, 06 Mar 2012 10:19:27 GMT

Hi!

I know meaning of operators.

Have I understood how to define sets:

Count({

(

$<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

)

(

$<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

)

}

Distinct Customer)

In that expression I think I define 2 sets and both return 1 and end result is 1.

In this case:

set1 = {1}

set2 = {1}

set1-set2 = {1}

Or havent I understood how to define sets?

Exclusion in Set Analysis

CELAMBARASAN — Tue, 06 Mar 2012 10:22:13 GMT

Hi,

You mean it return count as 1?

Celambarasan

Exclusion in Set Analysis

CELAMBARASAN — Tue, 06 Mar 2012 10:26:02 GMT

Hi,

In expression2 - means

Get the records which satisfies this

(

$<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

)

and subtract the records which satisfies the below

(

$<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

)

Example:

v_Passive=Jan 2011,v_Passive12=Dec 2011

v_RollingStart=May 2011,v_MonthEnd=Mar 2012

Then Exclusion operator gives the result for

Jan 2011 to April 2011

Celambarasan

Exclusion in Set Analysis

jagan — Tue, 06 Mar 2012 10:26:45 GMT

Hi,

Try this expressions

Count({<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"} - {">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>} Distinct Customer)

Count({<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>} Distinct Customer) -

Count({<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>} Distinct Customer)

Regards,

Jagan.

Exclusion in Set Analysis

miikkaqlick — Tue, 06 Mar 2012 10:32:41 GMT

Yes, I know what expression 2 means:

Get the records which satisfies this

(

$<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

)

and subtract the records which satisfies the below

(

$<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

)

And this doesn't work.

If I make 3 expressions to single table:

1. Count(($<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>) Distinct Customer)

2. Count(($<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>) Distinct Customer)

Count(

(

$<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

)

(

$<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

) Distinct Customer)

All three will return 1.

So first expression will return 1, second will return 1 and third calculates 1-1=1.

Exclusion in Set Analysis

CELAMBARASAN — Tue, 06 Mar 2012 10:39:13 GMT

Hi,

Then have you checked with this expressions separately

Concat({

$< Customer =

P({$<Product={'Things'}, InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}>})>}Distinct Customer ,',')

Concat

({$<Customer =

E({$<InvoicingDate={">$(=v_Passive12)"}>})> }

Distinct Customer,',')

You will know who all the customers are in common.

Celambarasan

Exclusion in Set Analysis

miikkaqlick — Tue, 06 Mar 2012 10:53:52 GMT

Hi!

Your first expression gives same results than mine expressions: all customers who have bought things between Passive24 and passive12 including customers who have bought things in rolling period.

Your second expression gives allmost correct results. I tried also that before I sended mail to here. Final result from that expression is -25124. Because that is normal minus-operation. First part will return number of customers who have bought things in passive-period (about 250 000) second will return number of customers who have bought things in rolling-period (about 270 000). Calculation goes like this: 250 000 - 270 000 = -20 000.

I will (hopefully) get right result if I write that second expression like this:

=If(

Count(

{

(

$<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

)

}

Distinct Customer)

Count(

{

(

$<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

)

}

Distinct Customer)>0,1,0)

Then I have to choose straight table and sum of rows as total mode.

But this seems too complicated and I would like to get simple set expression right.

Re: Exclusion in Set Analysis

Mon, 26 Nov 2012 09:49:54 GMT

Hi Celambarasan,

If : Set1={1,2,3,4}, Set2={3,4,5,6}

Is there any difference between the results of :

Set1/Set2 & Set2/Set1 ?

Regards

Re: Exclusion in Set Analysis

CELAMBARASAN — Thu, 29 Nov 2012 20:02:04 GMT

Hi,

There won't be any difference between these for this set operator both will be same.

Set1/Set2 equals Set2/Set1

Also Set1*Set2 equals Set2*Set1

and Set1+Set2 equals Set2+Set1

Only the result of - (Exclusion) operator varies.

Set1-Set2 not equals Set2-Set1

Regards,

Celambarasan

Re: Exclusion in Set Analysis

Thu, 29 Nov 2012 21:22:23 GMT

Hi,

thanks for the answer,

So,that means we can generalize and say that

always holds true:

Set1/Set2 equals Set2/Set1

Also Set1*Set2 equals Set2*Set1

and Set1+Set2 equals Set2+Set1

and the result of Set1-Set2 depends on the values in Set1.

You explained it quite well with the example.

Regards