topic How to find lowest value except 0. in QlikView

How to find lowest value except 0.

AbhijitBansode — Tue, 31 May 2011 11:14:16 GMT

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.

Re: How to find lowest value except 0.

montubhardwaj — Tue, 31 May 2011 13:29:36 GMT

Pl find attached. Hope that helps you.

How to find lowest value except 0.

pat_agen — Tue, 31 May 2011 13:48:12 GMT

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.

christian77 — Tue, 31 May 2011 15:46:47 GMT

Try this:

Since Sum is a field:

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

How to find lowest value except 0.

AbhijitBansode — Wed, 01 Jun 2011 04:53:47 GMT

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.

Re: How to find lowest value except 0.

spsrk_84 — Wed, 01 Jun 2011 07:16:37 GMT

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

How to find lowest value except 0.

christian77 — Wed, 01 Jun 2011 07:18:50 GMT

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.