topic Re: Question in applying Market Basket Analysis (Support value) in Qlikview in QlikView

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

arunqlik2view — Mon, 11 Jun 2018 12:26:03 GMT

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

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

YoussefBelloum — Mon, 11 Jun 2018 12:30:20 GMT

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

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

sunny_talwar — Mon, 11 Jun 2018 12:33:58 GMT

or may be this

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

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

arunqlik2view — Mon, 11 Jun 2018 12:54:47 GMT

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

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

arunqlik2view — Mon, 11 Jun 2018 12:56:37 GMT

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 .

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

YoussefBelloum — Mon, 11 Jun 2018 13:02:27 GMT

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

do you want to make it dynamic ?

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

sunny_talwar — Mon, 11 Jun 2018 13:11:35 GMT

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.

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

arunqlik2view — Mon, 11 Jun 2018 13:39:12 GMT

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 .

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

arunqlik2view — Mon, 11 Jun 2018 13:40:34 GMT

Hi youssef ,

Thanks for the reply,

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

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

sunny_talwar — Mon, 11 Jun 2018 13:43:08 GMT

May be this

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

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

arunqlik2view — Tue, 12 Jun 2018 01:18:33 GMT

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 .

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

sunny_talwar — Tue, 12 Jun 2018 01:37:36 GMT

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

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

arunqlik2view — Tue, 12 Jun 2018 02:04:21 GMT

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

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

arunqlik2view — Tue, 12 Jun 2018 06:39:59 GMT

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

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

arunqlik2view — Tue, 12 Jun 2018 06:41:21 GMT

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 .

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

sunny_talwar — Tue, 12 Jun 2018 09:13:41 GMT

Check here

Uploading a Sample

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

arunqlik2view — Tue, 12 Jun 2018 09:37:02 GMT

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

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

sunny_talwar — Tue, 12 Jun 2018 11:18:36 GMT

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