topic Re: Union and Rank (crossed sales) in QlikView

Union and Rank (crossed sales)

Fri, 12 Oct 2012 15:54:14 GMT

Hi,

I would like to study some crossed sales phenomenons but need help on the philosophical direction that should be chosen.

I made a product list in a straight table and would like to display as an expression the best product that is being sold together with each product.

For exemple:

Product	Best product sold
A	D
B	A
C	A
D	A

I don't know how to start my expression. Aggr + rank ? P in set analysis ? Both ?

Any help will be thankful !

Cédric.

Re: Union and Rank (crossed sales)

johnw — Fri, 12 Oct 2012 20:48:06 GMT

This example has much more than you need in it, but it was easier for me to add your chart to an existing example than to build a new example. I'm using firstsortedvalue() to pick the best match from the list.

Edit: Heh, oops. That's right. When firstsortedvalue finds more than one match it returns null. On the other hand, I'm not sure what you'd want to see in that case. Both products concatenated? Something else?

Edit 2: Never mind. I really should have looked at the chart before posting. It's just not working. I'll try to fix it.

Re: Union and Rank (crossed sales)

johnw — Fri, 12 Oct 2012 21:59:46 GMT

OK, I think it's working now, though it's even more complex. You can see that if two products have the same ranking, we show both of them. If the product isn't bought with anything else, we show nothing. The data model plays a big part, but with the data model as I've set it up, the expression for the most common second product is this:

concat(aggr(if(rank("Count")=1,"Second Product"),"Product","Second Product"),', ')

Re: Union and Rank (crossed sales)

Mon, 15 Oct 2012 09:02:06 GMT

Hi John,

Thank you for your answer and for the example. I will try to adapt your expressions to my model and let you know if it works.

Cédric.

Re: Union and Rank (crossed sales)

Mon, 15 Oct 2012 12:35:18 GMT

John,

So far, it seems to work with my model. I still have to complete your expression to get extra results, but the main part is ok.

Thanks a lot !

Cédric.

NB: if you have any idea about an other discussion I opened last week...

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