topic Min function in QlikView

Min function

Wed, 16 Nov 2011 11:03:44 GMT

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

Min function

jagannalla — Wed, 16 Nov 2011 11:19:08 GMT

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

Re: Min function

swuehl — Wed, 16 Nov 2011 11:21:13 GMT

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.

Min function

Wed, 16 Nov 2011 14:13:56 GMT

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.

Min function

swuehl — Wed, 16 Nov 2011 14:16:51 GMT

Sorry, I am not familiar with avg lowest price.

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

Re: Min function

Wed, 16 Nov 2011 14:34:45 GMT

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

Min function

swuehl — Wed, 16 Nov 2011 14:42:36 GMT

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

Re: Min function

Wed, 16 Nov 2011 14:44:41 GMT

It works, thank you !