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


    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