Error with expression : sum(if(Price=min(Price), 1, 0))

[adelmeire]

Hi everybody,

I need some help for the expression of the last column.

Provider	Product	Lowest price	#Product at the lowest price
ZeCompany	Bear	= min(Price)	= sum(if(Price=min(Price), 1, 0))
OtherCompany	Bear	= min(Price)	= sum(if(Price=min(Price), 1, 0))

A product has a price which change during the year, for example. For each provider, I'ld to know what is the lowest price (=min(Price), it's ok) and how many bears has been sold at this price (= sum(if(Price=min(Price), 1, 0)) but it doesn't work)

Does anyone has an idea how to write this expression?

Thank you for your help

[Shubham_Deshmukh]

Hi Adelmeire,

As per my understanding from your data, you want count of all Bears sold at min price.

Create variable : vMinPrice = min(Price)

Use exp : 

=Count(if(Price=vMinPrice,1))

Rather than using '1', if you have any ID corresponding to that product then it would be better to use.

Regards

[adelmeire]

Thank you for your reply Shubham.

I think your solution won't works, because the variable will calculate the min(Price) for all providers.

Here, I want to count how many products, for each provider, has been sold at the min price of this provider.

[Shubham_Deshmukh]

Ohh..My bad...my solution will give overall count not provider wise.
Can you share sample data?

[tresesco · Accepted solution]

Try like:

Sum(if(Price=Aggr(NODISTINCT Min( Price),Provider), 1,0))

[adelmeire]

Great! Thank you.

I forgot NODISTINCT.