Question in applying Market Basket Analysis (Support value) in Qlikview

[arunqlik2view]

Hi All,

I'm particulary interested in finding the Support value by Market Basket Analysis in Qlikview.

here are the details that might help you to understand my question/ problem better .

- Let's say  my data has 3000 different items 

- Total number of transactions are 1000.

- If I want to find the support value for 2 items namely ITEM A and ITEM X, then I will find how many of the transactions has both of these 2 (ITEM A,ITEM X) items sold in it , lets assume this value as AX = 40 transactions.

Now , the equation for finding the support value is  = (AX / Total number of transactions)* 100

                                                                                 = (40/1000) * 100

                                                                                 = .04 * 100

                                                                                 = 4

My real issue here is , How do I find this kind of association for all 3000 different items using Qlikview,  Can anyone please advice me on this question . This will be really great if someone can answers this.

Thanks in Advance,

Arun

[YoussefBelloum]

Hi,

you can use Set Analysis to make your calculation on a specific scope (AX), like this:

=sum({<Item={'A','X'}>}Measure)

and then divide it by the total

[sunny_talwar]

or may be this

(Count(DISTINCT {<Transactions = p({<Item = {'A'}>})*p({<Item = {'X'}>})>} Transactions)/Count(DISTINCT Transactions))*100

[arunqlik2view]

Is there way I can do this in a for loop.

[arunqlik2view]

Hi Sunny ,

Thanks for the reply , your equations give me the answer for the equation But how Do I this for all 3000 values in Qlikview, how do I use this in a for loop .

[YoussefBelloum]

what is the main purpose for putting this into a loop on the script ?

do you want to make it dynamic ?

[sunny_talwar]

This comparison will always be for 2 product at a time? Because if not then we are talking about a lot of permutation and combination of items which will make it tremendously difficult to handle.

[arunqlik2view]

Hi Sunny , Thanks for your reply ,

I used your syntax and made a slight change in it , so that I can use this syntax against all Items vs ITEM A in a bar chart and  it worked .I believe it is correct  But it would be great , if you can confirm it .

=(Count(DISTINCT {<Invoice.no = p({<Items= {'ITEM A'}>})*p({<Items>})>} Invoice.no)/Count(DISTINCT Invoice.no))*100  

Also , I Tried to make further more change by swapping 'ITEM A' as a dynamic value but it didn't work and the syntax is

=(Count(DISTINCT {<Invoice.no = p({<Items= {$}>})*p({<Items>})>} Invoice.no)/Count(DISTINCT Invoice.no))*100  

, Please advise how to make it dynamic .

[arunqlik2view]

Hi youssef ,

Thanks for the reply,

Yes , I want to make it dynamic . Please check my reply for Sunny's

[sunny_talwar]

May be this

=(Count(DISTINCT {<Invoice.no = p({<Items = p(Items)>})*p({<Items>})>} Invoice.no)/Count(DISTINCT Invoice.no))*100