topic Re: Expression to know the mix product in QlikView

Expression to know the mix product

andyrebs — Thu, 11 Oct 2012 13:13:47 GMT

Hello

I would have an expression in a chart, that can let me know what else custumers buy when they buy a specific product.

I attached for you perusal an excel file with three sheets.

Sheet 1 : customer details
Sheet 2: order details
Sheet 3 : product details

Is it possible to select a Family and view what else, the custumers those buy the selected family , buy.

I hope you can help me.

Thanks

Andrea

Re: Expression to know the mix product

Thu, 11 Oct 2012 14:11:17 GMT

Create another Table within your load scirpt:

OtherProductOrders:
LOAD [Order N°],
      [Cod. Product] as OtherProductCode
Resident Orders;
INNER JOIN
LOAD [Cod. Product] as OtherProductCode,
      [Desc. Product] as OtherProductDescr,
      Family as OtherFamily
Resident Products;

Then you can display i.e. OtherFamily in a listbox or diagram.

(Change "Orders" and "Products" to appropriate table names in your script)

Re: Expression to know the mix product

Thu, 11 Oct 2012 14:21:15 GMT

the trick here is to use set analysis to exclude the family selection.

Place products as your dimension.

add an expression to eliminate family ie count({<family=> value)

This will effectively give you the count of all products bought by someone in a particular family.

Re: Expression to know the mix product

Thu, 11 Oct 2012 15:10:33 GMT

This is nice for aggregation functions. But how would you display a list of Products / Families in this way?

Re: Expression to know the mix product

Thu, 11 Oct 2012 15:16:16 GMT

the unique products would be your dimension and you would have the added benefit of seeing a distribution which can be diplayed or graphed.

Re: Expression to know the mix product

Thu, 11 Oct 2012 15:22:24 GMT

Can you provide a quick demo application based on the attached sheet?

Re: Expression to know the mix product

swuehl — Thu, 11 Oct 2012 17:05:37 GMT

As I've understood the OP's request, he wants to filter the Companies to see only the ones that have ever bought a product of the selected family type.

If you use something like

=count({<family=> value)

you will clear the selection in family for this expression, so if you use this as expression in a chart table, and you have made a selection in field family, you will essentially address all records for your calculation.

I don't think this will result in something the OP wants to see, it does not filter the Companies that have ever bought a product from family selected.

To achieve this, you need to tell QV to implicitely select those Companies, that have a relation with the selected family. Then you need to clear the selection in family, to allow one to see all products bought by those Companies.

You can use a implicite field value definition using the p() function for this:

=count({<Company = p(), Family= >} [Order N°])

Using this in a chart with dimension Company and Product desc, you will see only the Companies that ever bought products from the selected family, but you will see all products they bought.

Re: Expression to know the mix product

andyrebs — Fri, 12 Oct 2012 09:04:21 GMT

Hi,

I am sorry for the delay. Stefan, you have understood. I tried your expression and it works.

Do you know which is the best way to view this kind of Analysis? Maybe a particular chart or graph or something else.

I need to ask another question, Which is the per cent version of your expression, how I can convert your expression in percentage?

Many many thanks

Andrea

Re: Expression to know the mix product

swuehl — Fri, 12 Oct 2012 09:23:13 GMT

I am not sure I understand: you want to display a ration, but a ratio defined by what?

Re: Expression to know the mix product

andyrebs — Fri, 12 Oct 2012 10:29:47 GMT

by the total of the expression.

Something like, sum(Value)/sum(total(Value))

Re: Expression to know the mix product

swuehl — Fri, 12 Oct 2012 10:35:35 GMT

So why not just use

=count({<Company = p(), Family= >}[Order N°]) / count(total {<Company = p(), Family= >}[Order N°])

=count({<Company = p(), Family= >}[Order N°]) / count(total<Company> {<Company = p(), Family= >}[Order N°])

and format the number on numbers tab as percentage.

Re: Expression to know the mix product

andyrebs — Fri, 12 Oct 2012 11:19:43 GMT

2nd Expression works, first one, that I had tried before asking you, doesn't.

Thanks again.

I think this kind of analysis is very interesting, but maybe you know something more usefull.

We want to cut some products, but we don't know the effects that this razionalization should has on our sales.

It could be interesting even a pivot chart? with as dimension family and inside again the families those are bought with the first family. I know.. My reasoning is complex, but I hope I understand me, again!

Re: Expression to know the mix product

swuehl — Fri, 12 Oct 2012 11:29:36 GMT

You can check these samples and see if they match your requirements:

http://community.qlik.com/message/162179#162179

http://community.qlik.com/message/225432#225432

Re: Expression to know the mix product

andyrebs — Fri, 12 Oct 2012 11:41:42 GMT

Thank you Stefan,

unfortunately I have the free version and I can not open the example files, nevertheless You was very very kind.

Best regards

Andrea