Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
miikkaqlick
Partner - Creator II
Partner - Creator II

Exclusion in Set Analysis

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?

11 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

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

miikkaqlick
Partner - Creator II
Partner - Creator II
Author

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?

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     You mean it return count as 1?

Celambarasan

CELAMBARASAN
Partner - Champion
Partner - Champion

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this expressions

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

OR

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

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

Regards,

Jagan.

miikkaqlick
Partner - Creator II
Partner - Creator II
Author

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)

3.

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.

CELAMBARASAN
Partner - Champion
Partner - Champion

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

miikkaqlick
Partner - Creator II
Partner - Creator II
Author

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.

Not applicable

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