If Price is a number then the syntax should be
This will work in some cases since set analysis will give you a global minimum price and not a different minimum price per row. You might also have to look into using the aggr() function to help make the table. To help you more please post an example of the table you want to generate.
Here's the table (see also attached qvw):
Comptetitor Model Price ABC 1 100 DEF 1 125 GHI 1 150 ABC 2 236 DEF 2 234 GHI 2 201 ABC 3 110 DEF 3 124 GHI 3 109
I have created a pivot table to compare the different prices pr. model, that the competitors offer, but now I want a table that simply shows what competitor has the cheapest price for a given model.
In the attached qvw file, the expression min(Price) correctly shows the lowest price for all models Model 1 = 100, Model 2 = 201, Model 3 = 109.
But my problem is that I don't know how to get the Competitors name in their with the lowest price.
I have inserted you suggestion, but as you correctly expected, it displays only ABC for model 1, because 100 is the lowest price in the entire set. I have played a bit with the aggr function, using Model as a dimension in it, but I can't seem to get it nailed.
How should I use the aggr function to accomplish what I want to do?
MinPrice.qvw 138.2 K
I managed to find an answer in another thread: http://community.qlik.com/message/186214#186214
By placing the competitor name in a dimension instead of an expression, I could get the lowest price pr. competitior by using this expression: If(Price=min(TOTAL <Model> Price),Price)
In the Competitor field put the following expression:
concat(distinct aggr(if(min(Price)=min(total <Model> Price),Competitor),Competitor,Model),', ')
I added the concat() function in case there is a tie between 2 competitor. In that case you will get a list of the competitors. The aggr helps to compare the price of each competitor and model with the total min price of each model.