Aggre function with sum and without sum

[#1]

=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

[jonathandienst]

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.

[#3]

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

[Kushal_Chawda]

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.

[jonathandienst]

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

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