Pick lowest positive value when Minimum value is -ve

[#1]

I am calculating Min Average Price (for Dimension1)  based on the following calculation

min(

aggr(sum([Sales]), [Dim1], [Dim2], [Dim3])

/

aggr(sum([Qty]), [Dim1], [Dim2], [Dim3])

)

The hierarchy is Dim 1>Dim2>Dim3. For a particular value for Dim1, there will be multiple Dim2 values and so on.

Idea is to get the Average price at Dim3 level and take the minimum of that.

In some cases, the Min price comes as negative. In that case, I want to take the lowest Positive Average price.

Please advise how to implement.

[#2]

Request forum experts to kindly assist

[#3]

PLease refer to the sample data below

Note : I donot have Average Price in source data, I am calculating it at Qlikview level.

Dim1	Dim2	Dim3	Sales	Qty	Avg Price
A	aa	xx	213	23	9.26
A	ab	xy	456	43	10.60
A	ac	zz	1200	-12	-100.00
B	bc	yz	89	-2	-44.50
B	bd	xx	23	23	1.00
C	cd	yy	65	54	1.20
C	ce	zz	567	12	47.25
C	cf	xz	345	98	3.52

In the above case, I would like the result to be

Dim 1	Min Avg Price
A	10.6
B	1
C	1.2

With the formula

min(

aggr(sum([Sales]), [Dim1], [Dim2], [Dim3])

/

aggr(sum([Qty]), [Dim1], [Dim2], [Dim3])

)

I am getting result as

Dim 1	Min Avg Price
A	-100
B	-44.5
C	1.2

So I want to take the lowest positive value as the Min Avg price

Please help.

[martinpohl]

use this:

min({<Qty={">0"}>}

aggr(sum([Sales]), [Dim1], [Dim2], [Dim3])

/

aggr(sum([Qty]), [Dim1], [Dim2], [Dim3])

)

but for A the min average price is 9.26 (from the first line)

Regards

[tresesco]

Find attached qvw.

=Min(If([Avg Price]>0,[Avg Price]))

[#6]

Martin

Thanks for your help.

But the condition  {<Qty={">0"}>} works at the Dim1 level (where the final output is) .

For eg, against A, the sum of Qty is 23+43-12 = 54. So it doesnot work the way the requirement is.

The need is to check whether the quantity and sales are positive at the Dim3 level

[martinpohl]

No, in that case QlikView only sums the values from qty > 0 so 23-43 = 66.

You can add Sales={">0"} in the formula