Qlik Community

QlikView Documents

Documents for QlikView related information.

Aggregation in Qlikview

Partner
Partner

Aggregation in Qlikview

AGGR IN QLIKVIEW



Aggr Statement can be said as a virtual table,
with one expression groped with one or more dimension so that we can
get a grouped results on the basis
of that expression

There is a special function for advanced aggregations:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})


In case in a chart we want to dispalay result according to Aggr Function

Let us suppose we want to see the average sale as per the salesperson
and as comparison to the dimension Region Wise.

In case we are using the simple QV expression we can get the Avg sale result as per

the given expression
(sum(sales)/count(Order)
So it will give the Avg sales as per the no of Orders,But not as per the dimension Country which is the required result.
So here comes the role of Aggr Function.
So while working with the aggr we have to implement the following steps

1>Use the above expression for calculating the Avg Sales

ie-(sum(sales)/count(Order)

2>Now we have to display the result as per the salesperson for the particular region.

So the output will be
Aggr(sum(Sales)/ count(Order), Country, Salesperson)
which will give the average sales as per Salesperson over the partcular region.

Now we have to calculate the max among the averag value calculated so far as per the country.

Let us take an example-

SalesPerson,Region,Average Order Value By Region

A          ,North ,50
B          ,North ,25
C          ,North ,100
E          ,South ,125
G          ,South ,143

So in case we want to get the max result as per our virtul table that have been created using the Aggr function so far the Expression

will be something like this


MAX(Aggr(sum(Sales)/ count(Order),Country, Salesperson))


and the Output as per the above result will be



SalesPerson  ,Region , MaxOrder

C            ,North  ,100
G            ,South  ,143


Comments
Arjunarao
Honored Contributor II

Hi Kamal, It is a good document.Thank you for sharing us.

neetha_p
Honored Contributor

Hi Kamal,

Great work!!!

Partner
Partner

Thanks

0 Likes

Good one

0 Likes
Partner
Partner

Thanks Kush

0 Likes
kalisha89
New Contributor

Nicely explained

kalisha89
New Contributor

Nice One

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-09-11 05:15 AM
Updated by: