Min and aggr function are returning second lowest value when min value is zero

[#1]

Hi,

I am trying to get the minimum value using the expression shown below:

Min(Aggr(Price,Item_Code))

Minimum value obtained is correct when minimum value is non zero but if the minimum value is zero I am getting the lowest value greater than zero ie second lowest value.Is it possible to correct this?

Regards,

Amit

[hic]

Don't use the Aggr() function. You will most likely get the correct result if you use Min(Price) as expression and Item_Code as dimension.

If you really want the Aggr() function, you need to use an aggregation function as first parameter in the Aggr() function.

See also: When should the Aggr() function NOT be used?

HIC

[Gysbert_Wassenaar]

Why are you using the aggr function? What that does it create a virtual table with Prices aggregated by Item_Code.You didn't specify an aggregation function to use inside the aggr so the only() funciton is used by default. f there are several prices for an Item_Code then only(Price) will return null since there are several possible Price values. That's likely why your 0 value isn't returned.

Check if the aggr is necessary. Try simply min(Price). If you do need the aggr then think hard about what kind of aggregation you need inside the aggr: min(aggr(avg(Price),Item_Code)... or max(Price)... or(sum(Price)... I have no idea.

[#4]

Thanks Henric and Gysbert for your reply.

I was using a sum function also in the expression:

Min(Aggr(sum({$<Field1=,Field2=>}Price),Item_Code)) 

Here I am trying to find the minimum price of an item. This function along with isnull and rangemin function is used in axis limits of bubble chart which has a reference line. I am not getting correct minimum value if its zero hence few bubbles which will fall on zero x axis were not visible.

Thanks,

Amit

[hic]

Is Item_Code a dimension in the chart? If so, you should just use Min(Price).

If it is not, you should put Min(Price) inside the Aggr(). Then you will get the minimum price per item. But, you need to determine what you want to show: Each bubble may perhaps represent several items, So, do you want the lowest Min(Price)?

     Min(Aggr(Min(Price),Item))

Or do you want the average Min(Price)?

     Avg(Aggr(Min(Price),Item))

HIC

[#6]

Hi Henric,

Thanks a lot for your help.

We were able to figure out the problem. The issue was the absence of datapoint for a certain combination. Hence we added zero as a datapoint for those combinations.

Regards,

Amit