Getting second lowest value from Aggr doesn't work correctly

Mon, 15 Aug 2011 13:44:56 GMT

I'm trying to get the second lowest price for each product and the solution was clear in my mind so no problems there. What happened however was that the aggregation table for some reason seems to create one extra minimum row, or then the min() function doesn't work as it should.

My expression is as follows:

=min(TOTAL <Product>
     aggr(
          sum(Spend)
          /
          sum(QTY)
     , Product, Company
     )
, 2
)

This does not give the second lowest price but instead the lowest price, ie. the same result comes if I change the parameter 2 to 1. If I change the parameter to 3, I get the second lowest price.

Here is the resulting table showing the minimum price with different parameters:

As you can see, parameters 1 and 2 return the lowest price and 3 the second lowest. Why is the function doing this, have I done something wrong or do I miss some important thing here? Of course the quick and dirty solution is to use parameter 3 instead of 2, but since I don't have a clue what is going on I can't do that. I couldn't be sure it would always work as it should even though it seems that way.

Any ideas what causes this or is this a known problem? I'm using QV10.00.8935.7 SR2.

EDIT: I've included an example file if that makes it easier to see the problem.

Message was edited by: Henry Backman

Getting second lowest value from Aggr doesn't work correctly

Thu, 27 Oct 2011 06:16:51 GMT

This is a bug in QlikView software and will be fixed at some point. Only workaround is to use the parameter n+1 to get the rank n.

topic Getting second lowest value from Aggr doesn't work correctly in QlikView

Getting second lowest value from Aggr doesn't work correctly

Getting second lowest value from Aggr doesn't work correctly