topic Re: Aggre function with sum and without sum in QlikView

Aggre function with sum and without sum

Wed, 12 Apr 2017 07:08:43 GMT

=SUM(aggr(SALES, SALESPERSON, PRODUCT))

=SUM(aggr(SUM(SALES), SALESPERSON, PRODUCT))

Can I ask what is the difference between the first function and second function. On one dataset, both functions give me the same result. On another dataset, both functions give me a different result. There is a difference but can someone explain to me in simple words? thanks

Re: Aggre function with sum and without sum

jonathandienst — Wed, 12 Apr 2017 07:39:37 GMT

Aggr() returns an n-dimensional matrix or hypercube (where n is the number of dimensions supplied) - evaluating the inner expression over each cell in the hypercube.

In the first case, SALES will be set to null if there is more than one possible value for any combination of SALESPERSON/PRODUCT. The inner expression is =SALES which QV treats as =Only(SALES).

The second expression will sum the values, so it returns a value for every cell.

I suspect that your first dataset has only one possible value of SALES for each combination of the dimensions and your second dataset has more than one possible value for one or more combinations of the dimensions.

Re: Aggre function with sum and without sum

Wed, 12 Apr 2017 07:48:09 GMT

Hi sir, thanks for your explanation. What about =aggr((SALES), SALESPERSON, PRODUCT) ?

Re: Aggre function with sum and without sum

Kushal_Chawda — Wed, 12 Apr 2017 08:07:35 GMT

see the difference

First expression will not be evaluated when there is two lines for combination of Salesperson and product, because as explained by Jonathan , your first expression is nothing but =SUM(aggr(only(Sales), SalesPerson, Product)). So when there is a two lines for combination of Salesperson and product it will return NULL, due to outer aggregation Sum(NULL) will become 0.

Where your second expression simply performing Sum(Sales) for each combination of Salesperson and product,because you are performing Sum in inner expression.

Re: Aggre function with sum and without sum

jonathandienst — Wed, 12 Apr 2017 09:18:10 GMT

>>What about aggr((SALES), SALESPERSON, PRODUCT) ?

This is the same as aggr(SALES, SALESPERSON, PRODUCT). Parentheses won't change anything.