# Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
Employee

## The Aggregation Scope

When a calculation is made in a QlikView session, it always involves an aggregation over the relevant data records. But which records are relevant? What is the scope of the aggregation?

This seems like a simple question, but there are in fact quite a few things that could be said about it.

Normally, there are two different restrictions that together determine which records are relevant: The Selection, and – if the formula is found in a chart – the Dimensional value. The aggregation scope is what remains after both these restrictions have been taken into consideration.

But not always…

There are ways to define your own aggregation scope: This is needed in advanced calculations where you want the aggregation to disregard one of the two restrictions. A very common case is when you want to calculate a ratio between a chosen number and the corresponding total number, i.e. a relative share of something.

In other words: If you use the total qualifier inside your aggregation function, you have redefined the aggregation scope. The denominator will disregard the dimensional value and calculate the sum of all possible values. So, the above formula will sum up to 100% in the chart.

However, there is a second way to calculate percentages. Instead, you may want to disregard the the selection in order to make a comparison with all data before any selection. Then you should not use the total qualifier; you should instead use Set analysis:

Using Set analysis, you will redefine the Selection scope. The set definition {1} denotes the set of all records in the document; hence the calculated percentages will be the ratio between the current selection and all data in the document, split up for the different dimensional values.

In other words: by using the total qualifier and set analysis inside an aggregation function, you can re-define the aggregation scope.

• To disregard the dimensional grouping – Use the Total qualifier
• To disregard the selection – Use Set Analysis

The above cases are just the basic examples. The total qualifier can be qualified further to define a subset based on any combination of existing dimensions, and the Set analysis can be extended to specify not just “Current selection” and “All data”, but any possible selection.

And, of course the total qualifier can be combined with Set analysis.

A final comment: If an aggregation is made in a place where there is no dimension (a gauge, text box, show condition, etc.), only the restriction by selection is made. But if it is made inside a chart or an Aggr() function, both restrictions are made. So in these places it could be relevant to use the total qualifier.

HIC

Further reading related to this topic:

What does the TOTAL qualifier do?

Totals in Charts

A Primer on Set Analysis

Specialist II

Hi HIC

Thank you for such a nice blog.

i have read your, almost all blog post, and learnt a lot from your blogs.

Khan

20,231 Views
Not applicable

please have a look at the expression below.

Sum({\$<Region= {'West'} >} Total <City> Sales)

what is this expression calculating?  Is <city> means {<city=>}?

20,231 Views
Specialist II

Hi, Ikram.

Normaly expressions group data by dimensions, but this expression will ignore the dimensions, because you used the TOATL Keyword, TOTAL means ignore the dimension,(or dont group data by dimension).

and you passed the <City> field to expression, its means Group Sales by City.

{<City=>} means  = all cities.

<City> Means Group by City.

Hope it helps.

20,231 Views
Employee

Ikram

The {\$<Region={'West'}>} is your Selection scope.

The total <City> is your Dimensional scope, meaning that the aggregation will disregard all dimensions except City.

HIC

20,231 Views
Contributor III

Very good your explanation. In fact, selections, aggregations, set analisys, and how the combinations of them affect the result of expressions in different objects (Charts, Tables, ) would probably give a book only on this thema, particularly Pivot Charts.

And the Aggr() is quite a mystery to me.

Thanks.

20,231 Views
Employee

You're right that there is enough information to write an entire book about these things...

Concerning the Aggr() function - It can make things look extremely complicated, but it is really quite straightforward: It is a nested aggregation. Example:

If   Avg( Sales ) calculates the average sales value per record in the database, then

Avg( Aggr( Sum( Sales ), OrderID )) calculates the average sales value per order.

That is, the Aggr function creates "a virtual table" with one line per order - and then the Avg() calculates the average of the lines in this table.

Take a look at http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/07/aggr and I am sure it will become clearer.

HIC

20,231 Views