1 Reply Latest reply: Oct 27, 2011 2:16 AM by Henry Backman RSS

    Getting second lowest value from Aggr doesn't work correctly

      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:

      min_aggr_problem.png

       

      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