compare the two functions

[wanyunyang]

Hi guys,

I'm confused by these two functions:

SUM(Aggr(SUM(Amount), Group))

SUM(Amount)/COUNT(DISTINCT Group)

What is the difference between them?

Thanks in advance!

[rubenmarin]

Hi Wanyun, the first one is doing an addition of the Amounts of each group.

The second it's doing all the amount divided by the different number of groups, like an average of amount per group.

[wanyunyang]

Thanks Ruben. Are SUM(Aggr(SUM(Amount), Group)) and Aggr(SUM(Amount), Group) the same?

[Digvijay_Singh · Accepted solution]

If you have following values -

Group  Amount

A 100

B 500

C 400

The above table is virtual output of Aggr(Sum(Amount),Group) as well, if you surround it with one more SUM as  SUM(Aggr(SUM(Amount), Group)), its output is 1000, means sum of all Amounts of virtual table you see above.

If you use SUM(Amount)/COUNT(DISTINCT Group) in text box then it will result into - 1000/3 (we have 3 distinct group)

If you use this expression in chart with Group as dimension then each row will have its Amount/1( each row has one count only)

So same expression show different results based on where exactly its being used, in text/image boxes or in the chart, different dimensions will show different results. Use of TOTAL inside expressions as well make difference as it indicates at which dimension level the aggregation is being performed.

Hope it helps!

[rubenmarin]

Not really, the second can return many different values, so it will show as null if Group it's not one of the dimensions.

The first one does the sum of all those values, returning only one value, it can work with or without 'group' as dimension.

[wanyunyang]

Thanks Digvijay!