set expression to only show lowest price from one particular company

[coloful_architect]

I have a table with these fields 

product code, price, product type, company, price

for same product type, there are different product codes by different companies.

I want to build a table chart to show all the products by company x as long as it has the lowest price

for example, if the conceptual raw table is like 

Product Code	Price	Product Type	company
A	1	apple	x
B	3	apple	y
C	0.5	apple	z
D	3	orange	x
E	5	orange	y
F	2.1	berry	x
G	7	berry	y

the table I want to render is 

Product Code	Price	Product Type	company
D	3	orange	x
F	2.1	berry	x

since company x only has the lowest prices on orange and berry, then the table only picks 2 products out.

what set expression at price  I need to build.

pls advise.

thanks 

[Chanty4u]

Try this

=aggr(if(price = min(total <Product Type> price), Product Code), Product Type, company)

[Rohan]

Hi,

For your scenario, you should try using Aggr() with min() in the dimension column. like :

Aggr(Min(Price),Product_Type,Company).

Try this & revert if any issues.

Regards,

Rohan.

[Ahidhar]

try this

min({<company={'x'}>}aggr(If(Price = min(total <[Product Type]> Price) and company='x',Price),[Product Type],company))

[coloful_architect]

Thanks Ahidahr, what if I want to add one more condition to exclude those data rows that company x is exclusive? 

if company x only produces one product, its price is by default the lowest. I dont want my table to show those data.

so how do we exclude that condition based on your set expression? please advise.

[Rohan]

Hi @coloful_architect ,

Generate a flag from load script, where you flag exclusive products. by :

left join(Data)

Load Product_Code,

Count(distinct Company) as Count.

Resident Data group by Product_Code;

Then in set analysis, Exclude Count-={"1"}. Hope this helps.

Regards,

Rohan.