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

[arunqlik2view]

Hi Sunny,

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

I just got 100  as output. I don't think it did the trick . May be I will use your first Syntax. I know this sort of Analysis can be done in R, I thought it would be nice to have it as a viz form in Qlikview.

Thanks for your help. You doing a great job by answering the questions in the forum. Keep rocking pal.

Thanks,

Arun .

[sunny_talwar]

Arun -

I think it might help if you are able to share a sample where I can see what you are trying to do. Right now, I am just throwing different ideas of what might work. Would you be able to share a sample?

Best,

Sunny

[arunqlik2view]

Sure Sunny, I will upload the sample file. Please give me some time . Thanks.

[arunqlik2view]

Hi Sunny , I have uploaded the sample QVW file. Please check in to it.

[arunqlik2view]

Looks like it is not uploading my files , I just dropped them in to this text editor box. I don't find an upload button. What am I missing here ?  Please guide me .

[sunny_talwar]

Check here

Uploading a Sample

[arunqlik2view]

Hi Sunny,
Please check for the attached files . All I want to have one dynamic Item compared  (Support) with all other items in the Data. Your first syntax , I have used it in a text box and it shows the correct output.

=(Count(DISTINCT {<Invoicre.no = p({<Items = {'Blue Jeans'}>})*p({<Items = {'white cotton socks'}>})>} Invoice.no)/Count(DISTINCT Invoice.no))*100

- But I'm, looking to have a Dynamic item = The user will have the power to choose this item in the list box or so

- Then , this dynamic items is compared with all the other item.

When I try  with the syntax , qlikview doesn't give correct output.

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

Please check if anything can be done here.

Thanks & Regards

Arun

[sunny_talwar]

It seems to me that we have made this expression more complex then it needs to be.... I am not entirely sure if this is what you want, but can you try this

=(Count(DISTINCT Invoice.no)/Count(DISTINCT {1} Invoice.no))*100

Here make selection in any single item to see if this gives you what you want. If for some reason it doesn't... please let me know what do you expect to see for the selection you made and we will go from there