16 Replies Latest reply: Jan 27, 2012 8:19 AM by María José Serrano

# Min value by two dimensions

Hello,

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

I mean I have this data structure

ProductSupplierAvg Price
A11.5
A20.8
A32.1
B13.2
B24.2
B33.8
C17.1
C28.2
C36.9

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

ProductSupplierAvg Price
A20.8
B13.2
C36.9

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

Can anyone help me?

MJ

• ###### Min value by two dimensions

Hi,

Image not displayed that you have attached.

Celambarasan

• ###### Min value by two dimensions

Hi,

Use this expression,

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

Celambarasan

• ###### Min value by two dimensions

Hi

try this

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

may it hepls you

thanks

rohit

• ###### Re: Min value by two dimensions

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

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

Find attached the images.

• ###### Min value by two dimensions

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

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

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

Hi MJ,

Is it working as your need?

Celambarasan

• ###### Min value by two dimensions

in dimension

Supplier

Product

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

hope this help

• ###### Re: Min value by two dimensions

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

• ###### Min value by two dimensions

Hi,

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

Celambarasan

• ###### Min value by two dimensions

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!

• ###### Re: Min value by two dimensions

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

Thanks a lot!

• ###### Min value by two dimensions

is it showing any error?