Union and Rank (crossed sales)

[#1]

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.

[johnw]

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.

[johnw · Accepted solution]

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"),', ')

[#4]

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.

[#5]

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