Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Help with market basket Analysis

Hello

I am working on a project of MBA and I am stuck on a formula.

I want to create a table with all the product sold and for each one the value of the basket where they are.

I am able to get the first measure:

=COUNT({<Product= >}DISTINCT NoBasket)

But first the second  I am not able to build it. I tried a lot of formulat like

=SUM({<NoBasket=P({<Product= >} NoBasket), Product= >} Amount)

but it does not work, it gives me the value of the product for the basket not the value of all the basket where it is.

Does anyone have any idea ?

Thanks for your help

1 Solution

Accepted Solutions
danielrozental
Honored Contributor II

Re: Help with market basket Analysis

Check this.

6 Replies
Not applicable

Re: Help with market basket Analysis

I am loosing hope. Is it even possible to do you it ?

Thanks

Highlighted
danielrozental
Honored Contributor II

Re: Help with market basket Analysis

Post a sample app to make it easier to help you.

Not applicable

Re: Help with market basket Analysis

Here is a sample I built

https://dl.dropboxusercontent.com/u/1687492/Test%20Basket.qvw

In the box in the bottom, I woul  like to calculate the value of the basket of each product. In the blue box there are tyhe expected value for each line.

Thanks for your help

Not applicable

Re: Help with market basket Analysis

Bottom line in my sample, it would be a formula doing the same as:

=IF(Product='A',SUM(Total{$<Basket ={"1","3"}>} Val), IF(Product='B',SUM(Total{$<Basket ={"2"}>} Val),IF(Product='C',SUM(Total{$<Basket ={"1","2"}>} Val),IF(Product='D',SUM(Total{$<Basket ={"4","3"}>} Val),IF(Product='E',SUM(Total{$<Basket ={"1","4"}>} Val))))))

but In my real application I have thousand of product so I cannot do it manually for all the possibilities.

Does anyone have an idea ?

Thanks

danielrozental
Honored Contributor II

Re: Help with market basket Analysis

Check this.

Not applicable

Re: Help with market basket Analysis

That's it. I need to learn more about agg functions

Thank you.