Aggr()

[NavinReddy]

Hi All,

Could some one help me

What is the purpose of AGGR and How it helps

Thanks In Advance

Niranjan

[swuehl]

Please check the reference manual and also

http://community.qlik.com/docs/DOC-3857

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-u...

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/21/a-historical-odyssey-the-doctors-speci...

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/06/a-historical-odyssey-qlikview-7

[VishalWaghole]

Hi Niranjan,

Aggr() is same like group by clause in SQL.

- Regards,

Vishal Waghole

[Anonymous]

Hi Niranjan,

AGGR() is advanced aggregation function that can be used to aggregate a other aggregate function.

Example...

You have country , customer, sales in your table,

If you provide country , customer as dimension and sum(sales) as expression in chart,

then sum() will aggregate sales on basis of country and customer,

while if you use another expression aggr(sum(sales),country), it  will return aggregated sales based on country only.

One more thing , by using aggr(), you can even use your expression as dimension....

Hope this helps..

Regards

Nitin

[Anonymous]

Airline	Plane	# of Passengers
Delta	747	10
Delta	737	20
United Airlines	747	10
United Airlines	737	20

In the above table the average # of passengers is 15  (60/4) but what if I wanted to find out the average # of passengers by Airline only.

To do so I can aggregate the table without doing it in the script by using the aggregation function.  When I type the function "Aggr( sum( [# of Passengers]), Airline)"  the result would look like the below table in theory.  So now when I do an average "Avg( aggr( sum( [# of Passengers]), Airlne))" I get the result of 30  (60/2).

Airline	# of Passengers
Delta	30
United Airlines	30

[#6]

Hi Micheal,

How to achieve this in a table. I have 2 fields 'Airline' and '# OF Passengers" and i want the sum of passengers across every unique Airline. How to achieve this in a table?

[Anonymous]

just take airline in dimension and sum(passengers) as expression...

Or if you want in script, then

Load distinct Airline,

sum(passenger) as passengers

from table name

group by airline;

will give your desired result...

Regards

Nitin

[#8]

Hi Nitin,

that works in case of a chart which i am well aware of. But where to write an expression in case of a table?

[Anonymous]

What do you mean by table>>

Table box or Straight table.

For table box you have to use calculation in script level only.

as mentioned in my previous comment.

and for straight table you can write expression as you know ...

Regards

Nitin

[#10 · Accepted solution]

Hi Niranjan,

Aggr() function is used to combine two aggregation functions.

syntax: aggr(expression, dimension)

It is used to making a temporary table in between two aggregation functions

ex. min(aggr(max(value),customer))

table is like this:

customer      value

A                 20

A                 30

B                 10

B                  15

C                  40

here aggr(max(Value),Customer) function making a temporary table like this:

customer    value

A               30

B               15

C               40

then min() function retrieves the record B   15 from the temp table

I think now you understood the concept of aggr() function

Regards,

Nuthan