Min function

[#1]

Hello,

I have a problem:

I have a table similiar to this example:

Article	Price	Quantity
A	50	200
B	60	250
C	70	400
A	100	200
B	95	250
C	120	400

I need to calculate the average of the lower price * quantity.

I don't know how to select the lower price for each article, I tried with

avg (Quantity) *  Min (Price)

but Min (Price) is calculate over all entries, and not for every specific Article.

Please help me

[jagannalla]

Use the below script may be it helps you...

Table1:

Load

Article,

Price,

Quantity,

Price * Quantity as data,

from table1;

ResidentData:

Load Min(Data) as  LowerPrice

Resident Table1

Group By Article

;

Article

[swuehl]

Not sure that I understood your complete requirement.

The min Price grouped by Article could be found by

min(total<Article> Price)

I think, but then... what is the next step in your calculation, could you give an example calculation with above data?

edit: if you want to calculate that result outside of that table e.g. in a text box, you probably need advanced aggregation, aggr() function.

[#4]

This solution is not working.....

LowerPrice is incorrect (and i can't understand why.....)

Another problem is that quantity and price are not in the same table (now I'm trying your solution in a different table with both field).

I need to compare average prices of one company to average lowest price of the market.

So I'm looking for a solution to calculate correct "avg lowest price" for each article.

[swuehl]

Sorry, I am not familiar with avg lowest price.

Could you calculate an example with above table data for me/us?

[#6]

OK, here is one real example:

Item Number	QTY	Competitor Price	My Price	Competitor Pice * QTY
68682	684	256,5	178,76	175446
113481	45	142,5	99,36	6412,5
113481	45	185	99,36	8325
131772	258	192,5	168,73	49665
131772	258	206,5	168,73	53277

I need to calculate the average competitor lowest price of these products that is (175446+6412,5+49665)/3, but I don't know how

[swuehl · Accepted solution]

So your QTY is alway the same for each Item Number?

Then you might try this:

=avg(aggr(min([Competitor Price])*only(QTY),[Item Number]))

Hope this helps,

Stefan

[#8]

It works, thank you !