Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Calculating the support value

Hello,

I've got the following sample data set:

Client, Product, Sales

1, Apple, 100

1, Mango, 200

2, Apple, 100

2, Carrot, 500

3, Mango, 300

4, Carrot, 200

5, Carrot, 100

5, Apple, 300

Description:

I try to calculate the Support Value. Support is the number of transactions that include items in the {A} and {B} parts of the rule as a percentage of the total number of transactions:

Support  =  (A and B) / Total = ("Apple" and "Carrot") / 5 = 2 / 5 = 0.4 (40%)

The following expression gets me the correct value in a text box:

=Count(DISTINCT {<Client = p({<Product = {'Apple'}>})*p({<Product = {'Carrot'}>})>} Client)/Count(DISTINCT Client)


Question:

On practice it could be much more transactions so I'd like to get some table as an output for top N combinations (by support) and play around with it using selections. Is it possible to do it? (don't want to set the products' names manually to each pair)

Expected output:

No, Combination, Support

1,  "Apple" and "Carrot", 0.4

2,  "Apple" and "Mango",  0.2

3,  "Carrot" and "Mango", 0.0

Regards,

Evgeniy

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Calculating the support value

Hi,

here's a first step :

with calculation done in script; 2 new tables :

item:

load Client, Product resident data;

inner join (item) load Client, Product as p resident item;

items:

load Client,Product & p as supitem resident item where  Product <p ;

drop table item;

supportitem:

load supitem , count(Client) as compte resident items group by supitem;

you can select combination in supitem, that selects Client in your data set

regards

View solution in original post

3 Replies
Highlighted
Specialist II
Specialist II

Re: Calculating the support value

Hi,

here's a first step :

with calculation done in script; 2 new tables :

item:

load Client, Product resident data;

inner join (item) load Client, Product as p resident item;

items:

load Client,Product & p as supitem resident item where  Product <p ;

drop table item;

supportitem:

load supitem , count(Client) as compte resident items group by supitem;

you can select combination in supitem, that selects Client in your data set

regards

View solution in original post

Highlighted
Creator
Creator

Re: Calculating the support value

Hi Olivier,


It seems awesome! Thank you so much! Especially thanks for the details!

And one more question by the way (even without your last step):

Imagine a business question: Which were combinations "affecting" on Friday?

Data:

Client, Product, Sales, WeekDay

1, Apple, 100, Thu

1, Mango, 200, Thu

2, Apple, 100, Thu

2, Carrot, 500, Thu

3, Mango, 300, Thu

4, Carrot, 200, Thu

5, Carrot, 100, Fri

5, Apple, 300, Fri

6, Carrot, 100, Fri

6, Apple, 300, Fri

6, Mango, 300, Fri

Would it be a correct solution based on your example? ( screenshot is attached)

Regards,

Evgeniy

Highlighted
Specialist II
Specialist II

Re: Calculating the support value

take care Evgeniy,

the cross join is made without considering the day,

it works with you example, but doesn't work if same client have different days purchase,

so in script weekday should be add in group by

regards