Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Iras
Contributor III
Contributor III

difference between Sum(Aggr(sum()) vs sum()

Can someone give a small example showing exact difference between  Sum(Aggr(sum()) vs sum() 

Labels (1)
5 Replies
vunguyenq89
Creator III
Creator III

You can think of Aggr() function as GROUP BY clause in a SQL query. This function doesn't return a single value, but an array of aggregated values grouped by one or more dimensions.

For example, if you use  Sum(Aggr(Sum(Amount), Office)) on the following data:

OfficeSalesmanAmount
LondonAlbert1000
LondonBob1500
ParisCarlos2000
ParisDavid2500
ParisEric3000

 

Then:

  • The Aggr(Sum(Amount), Office) part of the expression returns array [2500,7500] corresponding to [Sum(London), Sum(Paris)]
  • Sum(Aggr(Sum(Amount), Office) ) = 2500 + 7500 = 10000

In this simple example, two expressions Sum(Amount) and Sum(Aggr(Sum(Amount), Office) ) return the same value. Sum(Aggr(Sum(...)) is usually used when you want to redefine the computation scope by using Set Analysis, If condition or TOTAL keyword in the inner and outer Sum() functions

For this example, another aggregation function such asMax(Aggr(Sum(Amount), Office) ) = Max(2500, 7500) = 7500 (i.e find max sales amount between the two offices) would make more sense.

You can read more about Aggr function at https://www.analyticsvidhya.com/blog/2014/02/aggr/

Hope this helps!

Iras
Contributor III
Contributor III
Author

Hey 

thankyou for your reply but my questions is  the output of  Sum(Aggr(sum()) and  sum() should be same right  ?

In the example you have shown the Sum(Aggr(sum()) =10000 and if you just sum up all the amount its also 10000. 

is it true ?

 

Thanks and Regards 

Praneeth Pasari

 

 

Iras
Contributor III
Contributor III
Author

Taking this example 

RefXYZQ
HSE5321
PIP4315
SUP6781

 

If I want to find out the CV value and If the value is equal to ((x/y)*100) * (z-q)

CVsum(aggr((sum(X)/sum(Y))*100 *(sum(Z)-sum(Q));Ref)
CV((sum(X)/sum(Y))*100 * (sum(Z)-sum(Q)))

 

which formula should I use first one or the second one. Since both the values are giving different values. 

Iras
Contributor III
Contributor III
Author

Hey 

The link which you had sent me is directing to the question which I asked. 

 

Regards

Praneeth