3 Replies Latest reply: Jun 7, 2011 2:26 AM by susant Kumar swain RSS

    AGGR() calculating wrong results

    James Carpenter

      I used AGGR() to answer two questions from a dataset with 3.1 million rows and got inaccurate results. Also, the results change with different selections, when I would expect them to stay the same within a given dimension. The questions were: for each product, 1) what is the average premium per employee and 2) what is the medial premium per employee. It sounded like a good case for AGGR().


      The application is too large to upload but here are screenshots. (When I reduce the application, all versions of the expressions start to provide the same result. It's only with the full data that I see these unexpected results.)


      Note the expressions in the variable input box. The _1 version is what I used initially but gave wrong results. The _2 version gave the correct answer, but it requires that the expression include all the chart dimensions, which does not seem correct to me.


      The first screenshot shows the average and median calculated various ways, with the expressions above in an input box. The correct MEAN amount is represented by the 3rd and 5th expression columns. The correct MEDIAN is shown in the final column. This is based on calculations provided separately by Excel.



      The second shows the result of selection the first value in Product Catg. Notice how the calculations using AGGR() changed from the no-selections version:


      I really need to solve this, because AGGR() seemed quite promising for these kinds of calculations - if I can get it work correctly.