Scoping Selections with Aggr()

Summary: Selections can be made in Calculated Dimensions, although the result may not always be what is expected or desired.   The Aggr() function can be used to control what field(s) get selected.

The technique discussed in this post applies to both QlikView and Qlik Sense.  The screenshots shown are from QlikView.  Some of the visuals are a bit different in Qlik Sense, but the idea and expressions demonstrated are the same.

A downloadable example to accompany this post is available here.

Consider a listbox created with an <Expression> value of:

=Customer & ' -- ' & Country

A listbox constructed this way is useful for providing additional context or an  additional search.

Selections made in that listbox will make underlying selections in both Customer and Country.

The user is probably  expecting a selection in Customer only.  To limit the selection to Customer, add an Aggr() function to the expression:

 Customer & ' -- ' & Country

Only the Customer field is listed in the Aggr() dimension, so selections will be made only in Customer.

A side effect of adding Aggr() is that gray (unassociated) rows no longer display.  We can fix that with a bit of Set Analysis.

 only({1<Customer={"*"}>} Customer & ' -- ' & Country)

Now the listbox looks and behaves as expected.


Another place you may need Aggr() to control selection intent is chart Calculated Dimensions.

Hovering over a Salesrep value in the chart below gives a contextual popup that identifies Manager and Hire Date associated with the Rep.

The column was created as a Calculated Dimension:

& chr(10) & 'Reports to ' & [Sales Manager]
& chr(10) & 'Hire Date ' & date(HireDate,'YYYY-MMM-DD')

Clicking Michelle in the chart correctly selects her name as SalesPerson, but makes unexpected selections in HireDate and SalesManager.

I’m going to say that the dimension is “improperly scoped” and correct it by adding Aggr() to the Calculated Dimension.

 & chr(10) & 'Reports to ' & [Sales Manager]
 & chr(10) & 'Hire Date ' & date(HireDate,'YYYY-MMM-DD')

Selections will now be correctly limited to the “SalesPerson” field.


We’ve seen that Aggr() can narrow selections. We can widen selections as well.  This listbox will make selections in Customer, Country, SalesPerson and Year, even though only Customer is displayed in the listbox.

 ,Customer, Country, SalesPerson, Year)


We don’t have to include the display field in the selections.  In what I’ll call a  “backdoor associative search” , this expression will display Customer, but selects only the OrderID values associated with the Customer.

only({1}Customer )

It’s usually a best practice to pre-create Calculated Dimensions in the script, when possible, for performance reasons. Returning to our first example, we might create a new field in the script as:

Customer & ' -- ' & Country AS CustomerAndCountry

We can use the new field as a display value, but we want selections to be made in Customer.

 only({1}  CustomerAndCountry)


As a last example,  we can create  “bookmark” like alternatives; either new fields linked in the data model or advanced search at run time.

Here I’ve linked a hidden field named “Bookmark” into specific OrderIDs in the script.  I want selections to be reflected in the OrderID field.

=aggr(only({1}Bookmark), Bookmark,OrderID)

Here is an advanced search that presents a listbox of Customers who have placed at least one order with a value >50K.

only({1<OrderID={"=sum({1}OrderAmount)>50000"}>}Customer )

Aggr() can be a “heavy resource consumer” and has the potential to slow down your application. Use only when required and avoid using or benchmark the impact in large applications.  Calculated Dimensions can also be a source of slow performance, precalculate fields in the script when possible.

Download the  example qvw for this post .





10 thoughts on “Scoping Selections with Aggr()”

  1. For your initial listbox with Customer and Country
    I would use the listbox expression to show Country.
    No Aggr, no Calculated Dimension or extra combined fields.
    The expression Only({1} Country) does the trick.

  2. Great stuff, especially the ‘backdoor associative search’ and the bookmarks.

    The download link for the example qvw on this page doesn’t work for me. I did manage to download it from the Recipes page.

  3. Interesting use of Aggr. I’ve seen it abused so many time’s I’m always a bit wary whenever I encounter it. I’d never experimented with it much in list boxes and calculated dimensions though. I’ll have to add this to my bag of tricks.

  4. Hi,
    Re: Hovering over a Salesrep value – Qlik Sense

    In Qlik Sense, the whole text shows before Hovering Over. Is there a way to for this not to happen?

    The ‘Selection Part’ works perfectly.

    & chr(10) & ‘Reports to ‘ & [Sales Manager]
    & chr(10) & ‘Hire Date ‘ & date(HireDate,’YYYY-MMM-DD’)

    1. I don’t believe there is any way in the standard Sense table to suppress the automatic row height. There may be an extension available on that supports single row,

  5. I’d like to point out this little side effect (it happens at least in QlikView):
    I have a simple listbox that shows the values of the field Customer only, I select a value – say AAAAA – then I right-click on the listbox and click on “Select excluded”: the Current Selections (Ctrl-Q) box says: “Customer: NOT AAAAA”.
    If I select AAAAA in the listbox with the Aggr function then I right-click on the listbox and click on “Select excluded”, the Current Selections box would say: “Customer: 1599 of 1600”, which is not the same thing as above.

Leave a Reply

Your email address will not be published.