6 Replies Latest reply: Jun 1, 2011 3:18 AM by Christian Conejero

# How to find lowest value except 0.

Hi,

I want to find outh the minimum value of the sum(Amount) excluding 0.

below is the example, Shows listbox with expression and Frequency of occurance of each value.

 Product Sum(totalAmount) Frequency J1 4692236651 56765 C1 17058822429 787 D1 3451481830 6875676 O1 1947505462 56 S1 5303848371 576 M1 384027272 756 G1 1338660 576 Y1 1245244 576 T1 900437982 45 Y1 0 2 Z1 12 123

Here lowest value is 12 excluding 0. so i can find out thhis using

Min(Sum(total<Product>Amount),3). here i've use second parameter as 3 because 1st two values are 0. but this is not the case all the time. each time i may not know how many entries of 0 will be there,  how should i find out this minimum value.

• ###### Re: How to find lowest value except 0.

Pl find attached. Hope that helps you.

• ###### How to find lowest value except 0.

hi,

you need the aggr() function.

try this:

=min(if(aggr(sum(Amount),Product)>0,aggr(sum(Amount),Product)))

• ###### How to find lowest value except 0.

Try this:

Since Sum is a field:

min({\$<Sum={'<>0'}>} Sum)

• ###### How to find lowest value except 0.

Thanks all for replies.

As suggested in Min value.qvw app , filtering is done at modelling level.

but i can not go with this approach because i need 0 value entries for some other purpose.

pat.agen, your suggestion really hepls me and i'm able to achieve what i wnated.

Since sum is not  a field, it is calculated expression, i can not go with the 3rd suggestion.

• ###### How to find lowest value except 0.

do

sum(Amount)                 as TotalAmount,

Product                         as Product,

Month                           as Month,

Year                             as Year

group by Product, Year, Month

resident yourtable;

and you´ll have it as a field.

• ###### Re: How to find lowest value except 0.

Hi,

I tried in this way it is giving the correct result .Even i have not filtered any records at script level.

Min(aggr(IF(SUM( Total<Product>Sum)>=0,Sum(Total<Product> Sum)),Product))

Check this..

Regards,

Ajay