topic AGGR() calculating wrong results in QlikView

AGGR() calculating wrong results

jcarpenter9 — Fri, 03 Jun 2011 22:15:04 GMT

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.

AGGR() calculating wrong results

Tue, 07 Jun 2011 02:48:26 GMT

Still no answer.

AGGR() calculating wrong results

rwunderlich — Tue, 07 Jun 2011 05:40:07 GMT

With regards to your _2 statement about having to include all the columns. This is correct. By design, Aggr() ignores the chart dimensions. It is correct that you must list all dimensions to be used by aggr().

-Rob

http://robwunderlich.com/

AGGR() calculating wrong results

qliksus — Tue, 07 Jun 2011 06:26:39 GMT

The difference is becasue the Product Category selection in the expression Vmedian_1 is respected

your expression is "Median(aggr(sum(x),employeeid))" so this will disregard the Dimension Product Category which

you have taken in the chart But if you select that dimension in the list box this Expression will be calculated

for that selection also . this is the reason for change in the result