topic Re: Min and aggr function are returning second lowest value when min value is zero in QlikView

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

Tue, 11 Jun 2013 19:21:34 GMT

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

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

hic — Tue, 11 Jun 2013 19:31:00 GMT

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.

HIC

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

Gysbert_Wassenaar — Tue, 11 Jun 2013 19:35:55 GMT

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.

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

Thu, 13 Jun 2013 09:08:44 GMT

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

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

hic — Thu, 13 Jun 2013 10:02:42 GMT

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

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

Fri, 21 Jun 2013 12:06:30 GMT

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