topic Min value by two dimensions in QlikView

Min value by two dimensions

Fri, 27 Jan 2012 08:47:57 GMT

Hello,

I'm having a problem in retrieving the min value by two dimensions.

I mean I have this data structure

Product	Supplier	Avg Price
A	1	1.5
A	2	0.8
A	3	2.1
B	1	3.2
B	2	4.2
B	3	3.8
C	1	7.1
C	2	8.2
C	3	6.9

and I need to obtain for each product the supplier having the min average price, that is to say:

Product	Supplier	Avg Price
A	2	0.8
B	1	3.2
C	3	6.9

I've tryed a lot of combinations with min and aggr functions but with no results.

Can anyone help me?

Thanks in advance,

Min value by two dimensions

CELAMBARASAN — Fri, 27 Jan 2012 08:53:04 GMT

Hi,

Image not displayed that you have attached.

Celambarasan

Min value by two dimensions

CELAMBARASAN — Fri, 27 Jan 2012 09:19:52 GMT

Hi,

Use this expression,

=If([Avg Price]=min(TOTAL <Product> [Avg Price]),[Avg Price])

Hope this will help you.

Celambarasan

Min value by two dimensions

rohit214 — Fri, 27 Jan 2012 09:33:31 GMT

try this

=aggr(min([Avg Price]) ,Product)

may it hepls you

thanks

rohit

Re: Min value by two dimensions

Fri, 27 Jan 2012 10:33:55 GMT

Thanks you both!!

I've tryed with the expressions you mentioned and this one (=If([Avg Price]=min(TOTAL <Product> [Avg Price]),[Avg Price])) works perfectly for th example but not for my dashboard. When I include a new dimension It doesn't work.

Find attached a new example that includes Time Dimension. If I select a month the min value is correct but it is incorrect if no month is selected.

Thanks!

Min value by two dimensions

CELAMBARASAN — Fri, 27 Jan 2012 11:45:11 GMT

Hi,

I'm sorry to say this i cann't open your qv file because i'm using personal edition. Can u give me as a image.

Celambarasan

Re: Min value by two dimensions

Fri, 27 Jan 2012 11:50:19 GMT

Find attached the images.

Min value by two dimensions

CELAMBARASAN — Fri, 27 Jan 2012 12:00:17 GMT

Hi,

Here Avg Price means?..How your calculating i mean like its from datasource or calculating in chart if so based on what?

Re: Min value by two dimensions

Fri, 27 Jan 2012 12:08:34 GMT

I'm calculating in the chart as 'amount/quantity'

Find attached the table i'm using.

These are the expessions for each column:

Avg Price: =avg(Amount/Quantity)

Min Price: =min(TOTAL <Producto> Amount/Quantity)

Thank you so much!!!

Min value by two dimensions

CELAMBARASAN — Fri, 27 Jan 2012 12:17:28 GMT

Hi,

In that case use this expression for min price

Min(Total <Product> Aggr(avg(Amount/Quantity),Supplier, Product))

Celambarasan

Min value by two dimensions

CELAMBARASAN — Fri, 27 Jan 2012 12:45:17 GMT

Hi MJ,

Is it working as your need?

Celambarasan

Min value by two dimensions

SunilChauhan — Fri, 27 Jan 2012 12:55:14 GMT

in dimension

Supplier

Product

Min(Aggr(sum(Amount)/count(Supplier),Supplier, Product))

hope this help

Re: Min value by two dimensions

Fri, 27 Jan 2012 12:59:10 GMT

It works for the example but not in my dasboard, I'm checking it to find the problem....

Thanks a lot!

Re: Min value by two dimensions

Fri, 27 Jan 2012 13:01:44 GMT

but... this is not the average price... sum(Amount)/count(Supplier)...

Min value by two dimensions

SunilChauhan — Fri, 27 Jan 2012 13:01:52 GMT

is it showing any error?

Min value by two dimensions

CELAMBARASAN — Fri, 27 Jan 2012 13:05:41 GMT

Hi,

Could you explain me briefly about the problem that currently your facing now.

Celambarasan

Min value by two dimensions

Fri, 27 Jan 2012 13:19:52 GMT

I've found the problem!!! I had some product puerchases that didn't have supplier so the average price was 0.

The expression Min(Total <Product> Aggr(avg(Amount/Quantity),Supplier, Product)) works perfectly now.

Thank you so much for your help!