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: 
Not applicable

Aggr

Where and when to use Aggregation function...??

3 Replies
Not applicable
Author

Hi

below is aggr based on group by dept

Aggr(rank(sum(sal)),dept)

Below is aggr group by based on dept,empno

Aggr(rank(sum(sal)),dept,empno)

Below give dept top 2 sum(sal) values

if(Aggr(rank(sum(sal)),dept)<=2,sum(sal))



jonathandienst
Partner - Champion III
Partner - Champion III

Hi

The aggr function is used to evaluate an expression over a set of dimensions, in the same way as if the expression was being evaluated in a table/chart with that dimension.

Example 1:              =Aggr(Sum(Amount), StoreID)

This will evaluate the expression Sum(Amount) for each store ID. This expression returns a collection of values that could be used inside another expression, or as a calculated dimension.

Example 2:              =Max(Avg(SalesPrice), StoreID, SalesLine))

This will calculate the largest average price of all articles in any sales line in any store. This is the only way one aggregated function (SUm, Avg, Min, Max etc) can be nested inside another one.

Example 3:               =Sum(Aggr(Sum(SalesPrice) / Count(Articles), StoreID))

Used in a pivot table with dimension StoreID, this expression will return the ratio Sum(SalesPrice)/Count(Articles) for each line and at the total level, return the sum of the rows. Normally a pivot calculates the expression (eg Sum(SalesPrice)/Count(Articles)) at the total level and this may not be the same as the sum of rows.

Example 4:               =Aggr(Class(Sum(Amount), 1000), StoreID)

This will return a calculated dimension that will group amounts from stores into classes of 0-1000, 1000-2000 etc. You could use this with the expression Sum(Amount) to get a distribution of sales grouped by the total amount sold in each store. In other words, the graph shows the total sales in all the stores that sell 0 - 1000 in the first bar, the total sales in all stores that sell 1000-2000 in the next bar, and so on.

These are just some examples that come I hope will explain some of the concepts of aggr(). One thing to remember is that if you use aggr() inside a chart/table, you should include all the chart dimensions in the aggr() function, otherwise odd things happen.

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author