topic Re: Re: Pick lowest positive value when Minimum value is -ve in QlikView

Pick lowest positive value when Minimum value is -ve

Wed, 09 Jul 2014 09:30:24 GMT

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.

Re: Pick lowest positive value when Minimum value is -ve

Fri, 11 Jul 2014 08:05:12 GMT

Request forum experts to kindly assist

Re: Re: Pick lowest positive value when Minimum value is -ve

Fri, 11 Jul 2014 08:16:21 GMT

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.

Re: Pick lowest positive value when Minimum value is -ve

martinpohl — Fri, 11 Jul 2014 08:33:06 GMT

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

Re: Re: Re: Pick lowest positive value when Minimum value is -ve

tresB — Fri, 11 Jul 2014 08:59:26 GMT

Find attached qvw.

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

Re: Re: Pick lowest positive value when Minimum value is -ve

Fri, 11 Jul 2014 09:37:57 GMT

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

Re: Re: Pick lowest positive value when Minimum value is -ve

martinpohl — Fri, 11 Jul 2014 11:02:12 GMT

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

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