Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
Contributor II

compare the two functions

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!

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: compare the two functions

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!

5 Replies

Re: compare the two functions

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
Contributor II

Re: compare the two functions

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

Digvijay_Singh
Honored Contributor III

Re: compare the two functions

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!

Re: compare the two functions

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
Contributor II

Re: compare the two functions

Thanks Digvijay!