Finding people who bought a/few product(s) but not other product(s)

[Anonymous]

Hi there,

Much appreciate if anyone can show me some hints or the solution for the following situation.

TABLE_1:

LOAD * INLINE [

Area,Customer,Product

East,A,P1000

East,A,P1001

East,A,P1002

West,B,P1000

West,B,P1004

East,C,P1000

East,C,P1005

East,C,P1006

East,C,P1002

East,D,P1004

East,D,P1005

West,E,P1000

West,E,P1002

West,E,P1008

West,E,P1009

];

How to count by Customer and by Area

1) bought P1000 but NOT bought P1004

2) bought (P1000 & P1002) but NOT bought (P1008 & P1009)

Regards,
Chadwick

[swuehl]

Have a look at the p() and e() functions in set analysis.

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/se...

[Anonymous]

Thanks Stefan for your prompt response.

I already read the page your suggested but still don't understand how to apply the functions to the case.

I forgot to mention that two filter panes of the product list will be offered to the App users to select what product(s) were bought and what product(s) were NOT bought.

To make the two filter panes (Product) available, I tried to load the data twice as the following

Table_1	Table_2
TABLE_1: LOAD * INLINE [ Area,Customer,Product1 East,A,P1000 East,A,P1001 East,A,P1002 West,B,P1000 West,B,P1004 East,C,P1000 East,C,P1005 East,C,P1006 East,C,P1002 East,D,P1004 East,D,P1005 West,E,P1000 West,E,P1002 West,E,P1008 West,E,P1009 ];	TABLE_2: LOAD * INLINE [ Area,Customer,Product2 East,A,P1000 East,A,P1001 East,A,P1002 West,B,P1000 West,B,P1004 East,C,P1000 East,C,P1005 East,C,P1006 East,C,P1002 East,D,P1004 East,D,P1005 West,E,P1000 West,E,P1002 West,E,P1008 West,E,P1009 ];

In this way it allows users to select what products were bought and also what other products were bought, unfortunately, this cannot solve the original problem. I wonder whether this is the right direction to design the data model.

Regards,

Chadwick

[swuehl]

Maybe just load your fact table once and then the distinct list of products twice in two data islands, fields P1 and P2.

The set analysis could look like

=Count({<Customer = p({<Product = P1>})*e({<Product = P2 >}) >} DISTINCT Customer)

[Anonymous]

Hi Chadwick,

Try this:

1.)   Aggr(Count({<Product={'P1000'} - {'P1004'}>}Product),Customer,Area)

2.) Aggr(Count({<Product={'P1000'},{'P1002'} - {'P1008'},{'1009'}>}Product),Customer,Area)

Regards,

Rajan

[Anonymous]

Hi Stefan,

You meant to load the data like this?

Header 1
TABLE_1: LOAD * INLINE [ Area,Customer,Product1,Product2 East,A,P1000,P1000 East,A,P1001,P1001 East,A,P1002,P1002 West,B,P1000,P1000 West,B,P1004,P1004 East,C,P1000,P1000 East,C,P1005,P1005 East,C,P1006,P1006 East,C,P1002,P1002 East,D,P1004,P1004 East,D,P1005,P1005 West,E,P1000,P1000 West,E,P1002,P1002 West,E,P1008,P1008 West,E,P1009,P1009 ];

I tried the above but am afraid that it doesn't work. The two product filter panes are linked-up actually, so it cannot distinguish the "bought" item and the "not bought" item.

[Anonymous]

Hi Rajan, thanks for your inputs.

I tried your scripts in a KPI chart with the original inline table. However, the results seems are not correct.

1) the first script returned 1. Count of customer should return 3 (A, C, E)

2) the second script returned a hyphen . Count of customer should return 2 (A, C)

As supplemented in my other reply, users will be able to select the "bought" and "not bought" product(s) (product cannot be hard-coded)

Regards,

Chadwick

[Anonymous]

Hi Chadwick,

Try This:

Count({$<Customer=P({$<Product={'P1000'}>}Customer)*E({$<Product={'P1004'}>}Customer)>}Customer)

Regards,

Rajan

[swuehl · Accepted solution]

I tried the above but am afraid that it doesn't work. The two product filter panes are linked-up actually, so it cannot distinguish the "bought" item and the "not bought" item.

Right, that's why I suggested to use data island tables for P1 and P2:

TABLE_1:

LOAD * INLINE [

Area,Customer,Product

East,A,P1000

East,A,P1001

East,A,P1002

West,B,P1000

West,B,P1004

East,C,P1000

East,C,P1005

East,C,P1006

East,C,P1002

East,D,P1004

East,D,P1005

West,E,P1000

West,E,P1002

West,E,P1008

West,E,P1009

];

LOAD DISTINCT

Product as P1

RESIDENT TABLE_1;

LOAD DISTINCT

Product as P2

RESIDENT TABLE_1;

Use P1 to filter on the product that need to be included and P2 for exclusion.

This will work for single product selections in P1 and P2. If you select more than one product in P1 or P2, the set analysis will use an OR mode for the field values:

AND and OR

So it will for example exclude customers that show either one selected value in P2. Is that what you require? Or do you require that you only want to exclude customers that have a relation to all selected values in P2?

If  you want a full AND mode for the values in P1 resp. P2, then maybe use

Count(

{<Customer =  {"=GetSelectedCount(P1) = Count({<Product = P1>} DISTINCT Product)"}

                         -{"=GetSelectedCount(P2) = Count({<Product = P2>} DISTINCT Product)"}

>}DISTINCT Customer)

[Anonymous]

Hi Stefan,

Thumb up! Big thanks to your help. It works perfectly.

Regards

Chadwick