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

[Iras]

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

[vunguyenq89]

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:

Office	Salesman	Amount
London	Albert	1000
London	Bob	1500
Paris	Carlos	2000
Paris	David	2500
Paris	Eric	3000

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]

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

[premvihari]

https://community.qlik.com/t5/Qlik-Sense/ct-p/qlik-sense

[Iras]

Taking this example 

Ref	X	Y	Z	Q
HSE	5	3	2	1
PIP	4	3	1	5
SUP	6	7	8	1

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

CV	sum(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]

Hey premvihari, 

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

Regards

Praneeth