topic Re: Basket Analysis in QlikView

Basket Analysis

Mon, 31 Mar 2014 03:41:27 GMT

Hi All,

I have a transaction table with the following format:

SalesID SubProdID Amount

1 101 1000

1 102 2000

3 101 5000

3 102 6000

4 101 7000

4 102 8000

1 103 1000

3 103 2000

5 109 1500

Let us say I have a list box with SubProdID, I multi select 101 and 102. Now I want to display a chart that shows what is the likelihood of customers buying subprodid 103 when they buy 101 and 102 in the same transaction.

In this case we have 3 transactions 1, 3 and 4 that have 101 and 102 in the same transaction. Out of thes 1 and 3 also have 103 bought, but 4 doesn't have it. which means 2/3 of the customers who buy 101 and 102 buy 103.

I want to plot a graph that has the SubProdID as the dimension and the percentages of customers that bought the other products in that transaction as the measure.

103 66.6%

109 0%

Thanks,

Ram

Re: Basket Analysis

Mon, 31 Mar 2014 05:49:19 GMT

Wondering is that even possible? I am playing with alternate states and P(), but reaching nowhere. Any help would be great.

Re: Basket Analysis

whiteline — Mon, 31 Mar 2014 05:57:41 GMT

Hi.

Maybe Count({<SubProdID=, SalesID=P(SalesID)>} distinct SalesID)/Count({<SubProdID=>} distinct SalesID)

Re: Re: Basket Analysis

tresB — Mon, 31 Mar 2014 07:03:22 GMT

May be like attached sample?

Re: Basket Analysis

Mon, 31 Mar 2014 07:31:08 GMT

I am using personal edition. Could you share the script or chart expression?

Re: Re: Basket Analysis

tresB — Mon, 31 Mar 2014 07:42:00 GMT

Alternate state is used:

Expression for chart:

=Count({$<[SalesID]=p([State1]::[SalesID]),SubProdID -=[State1]::SubProdID>}DISTINCT SalesID)/Count({State1}total DISTINCT SalesID)

Assuming your selection is in State1 , dimension is SubProdID and chart is in default state..

Re: Basket Analysis

Mon, 31 Mar 2014 08:42:47 GMT

Do you still need help?

Some time ago i made some chart examples of basket analysis. I think, there was exactly the same problem

Of course sometimes your user would like to select more than 2 product and analyse co-selling on transactions with all selected ....

I remember, that we trainded few approaches.

So, if you still need help, let me know and i will try to find those apps.

regards

Darek

Re: Basket Analysis

Mon, 31 Mar 2014 10:28:31 GMT

Ok, i found demo prepared for basket analysis.

From technical point of view key to achieve the desired effect in our case was the use of AND-mode.

After turning it on (it is some work in load script to do, you will easy find instructions how to do it), expression to find results you need is as simple as:

count(distinct SalesID)/count(total distinct SalesID).

Benefits from this solution are, that you may choose as many products, as you want. You also may use NOT for your subset definition.

For example: get into account transactions where customer bought article 101, but not bought article 102....

I hope it helps.

Let me know if you need more information.

regards

Darek

P.S - of course there will be no problem to exclude 101 and 102 from the chart when you select 101 and 102, as result for them is always 100% (1 in my fast example

Re: Basket Analysis

Mon, 31 Mar 2014 21:45:02 GMT

Hi Dariusz,

Thanks for your inputs. tresesco's solutions is working perfectly and that is what I initially wanted, which is a MBA-Association analysis based on a single field.

Having seen your response I am wondering if I can extend the MBA-Association analysis to more than 1 field, I think that'll be useful.

Would you mind sharing the app? This AND is interesting, not sure what needs to be done at the script level.

regards.

Re: Re: Basket Analysis

Tue, 01 Apr 2014 07:43:05 GMT

I'am not sure if there is another solution than AND-mode if you would like to select ulimited number of products as AND basket criteria.

To turn it you have to load DISTINCT small additional table with field you would like to select in AND-mode.

Then (if load script is correct) you will be able to turn on AND-mode in listbox (Properties/Genelal).

Have fun with AND

I hope, it helps you.

regards

Darek