Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Request forum experts to kindly assist
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.
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
Find attached qvw.
=Min(If([Avg Price]>0,[Avg Price]))
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
No, in that case QlikView only sums the values from qty > 0 so 23-43 = 66.
You can add Sales={">0"} in the formula