topic Re: Aggr() in QlikView

Aggr()

NavinReddy — Tue, 24 Dec 2013 13:05:13 GMT

Hi All,

Could some one help me

What is the purpose of AGGR and How it helps

Thanks In Advance

Niranjan

Re: Aggr()

swuehl — Tue, 24 Dec 2013 13:17:40 GMT

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-used

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

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

Re: Aggr()

VishalWaghole — Wed, 25 Dec 2013 08:34:58 GMT

Hi Niranjan,

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

- Regards,

Vishal Waghole

Re: Aggr()

Anonymous — Thu, 26 Dec 2013 03:47:39 GMT

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

Re: Aggr()

Anonymous — Thu, 26 Dec 2013 05:03:44 GMT

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

Re: Aggr()

Thu, 26 Dec 2013 05:37:12 GMT

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?

Re: Aggr()

Anonymous — Thu, 26 Dec 2013 05:41:51 GMT

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

Re: Aggr()

Thu, 26 Dec 2013 05:45:30 GMT

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?

Re: Aggr()

Anonymous — Thu, 26 Dec 2013 05:49:41 GMT

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

Re: Aggr()

Thu, 26 Dec 2013 06:11:19 GMT

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

Re: Aggr()

Thu, 26 Dec 2013 07:16:39 GMT

Yup..i was referring to table box. In that case why would one use a table box? Straight table covers everything, so is there any functionality in table box which is not there in straight table or does it have any unique feature?

Re: Aggr()

Anonymous — Thu, 26 Dec 2013 08:20:38 GMT

Straight table covers all features of table box.

Only difference is we have to essentially define an expression in straight table while theres no need of it in table box.

Re: Aggr()

Thu, 26 Dec 2013 08:26:03 GMT

Hi Niru,

Aggr() function is a Group by function like Oracle, you can group by each customer wise, product wise,product wise.

you can get use aggregate function like Min,Max,Avg.

For Ex,

LOAD * Inline [

Customer_ID,Year,Month,Sales

101,2013,Jan,55

102,2013,Jan,56

103,2013,Jan,57

101,2013,Feb,65

102,2013,Feb,66

103,2013,Feb,67

101,2013,Mar,77

102,2013,Mar,78

103,2013,Mar,79

];

In Expression are,

Sum(Sales).

Aggr(Sum(Sales),Year)

Aggr(Sum(Sales),Customer_ID)

Aggr(Sum(Sales),Year,Customer_ID)