How to find lowest value except 0.

[AbhijitBansode]

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(total<Product>Amount)	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.

[montubhardwaj]

Pl find attached. Hope that helps you.

[pat_agen]

hi,

you need the aggr() function.

try this:

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

[christian77]

Try this:

Since Sum is a field:

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

[AbhijitBansode]

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.

[spsrk_84]

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

[christian77]

do

load

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.