Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New 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
Highlighted
Contributor III

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

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!

Highlighted
New Contributor III

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

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

 

 

Partner
Partner

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

Highlighted
New Contributor III

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

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. 

Highlighted
New Contributor III

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

Hey 

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

 

Regards

Praneeth