Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: difference between Sum(Aggr(sum()) vs sum()

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Iras

Contributor III

2019-10-11
05:56 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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

11,746 Views

5 Replies

vunguyenq89

Creator III

2019-10-11
08:50 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 as*Max(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

2019-10-14
05:26 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Partner - Creator

2019-10-14
05:55 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11,676 Views

Iras

Contributor III

2019-10-14
06:00 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

11,673 Views

Iras

Contributor III

2019-10-14
06:31 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hey premvihari,

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

Regards

Praneeth

11,664 Views

Community Browser