January 12, 2014

Understanding Aggr()

Aggr() is an important part of the toolkit that you need when building more complex expressions and calculated dimensions in Qlikview. This post attempts to demystify aggr() and show it can be used.

In a Qlikview chart, such as a straight table, the chart expressions are evaluated for all possible combinations of the dimension. An Aggr() function does the much same, so the expression:

Aggr(Sum(Sales), Region)

Produces a list of the summed values, one for each region. And this:

Aggr(Sum(Sales), Region, Period)

Produces an array of sales for each region/period combination. Three dimensions would construct a cube, and so on. 

You may use the aggr() expression anywhere that expects a list or array of values (technically an n-dimensional hypercube). This may include in a list box, a calculated dimension or in a nested aggregation. 

A nested aggregation is where one aggregation function (eg Sum, Min, Avg etc, not to be confused with the aggr() function) is contained within another. For example, lets say you would like to know the lowest possible summed value (eg Min(Sum(Sales))) for the sales outlets. Sum(Sales) will return a single value, but you need a list of Sum(Sales) by store. This is where aggr() comes in.  The expression becomes    

Min(Aggr(Sum(Sales), Store)) 

For another example, Lets say you want to know the lower 5% and upper 95% percentile of sales by Store:

Fractile(Aggr(Sum(Sales), Store), 0.05)
Fractile(Aggr(Sum(Sales), Store), 0.95)

If the data was arranged in months, and you wanted the expression should be calculated to each month (in a chart with month as a dimension):

Min(Aggr(Sum(Sales), Store, Month))
Fractile(Aggr(Sum(Sales), Store, Month), 0.05)
Fractile(Aggr(Sum(Sales), Store, Month), 0.95)

If you have more than one year, you would of course need to have a single year selected before using this chart/table, or you could do something like:

Min(Aggr(Sum({} Sales), Store, Month))
} Sales), Store, Month), 0.05)
} Sales), Store, Month), 0.95)

Aggr() expressions in a chart

When you use an aggr() in a chart, it is important to always include ALL the chart dimensions in the aggr() dimensions. This allows the expression to be calculated correctly in each cell in the chart or table.

This can present a problem if you are using calculated dimensions, as the dimensions in the aggr() statement have to be fields, not expressions. If the calculated dimension is simple and based on a single field, and each value corresponds to a single value of that field, then you may be able to use the field name in the aggr() expression.