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
