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:
=aggr(
Customer & ' -- ' & Country
,Customer)
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.
=aggr(
only({1<Customer={"*"}>} Customer & ' -- ' & Country)
,Customer)
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:
=SalesPerson
& 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.
=Aggr(
SalesPerson
& chr(10) & 'Reports to ' & [Sales Manager]
& chr(10) & 'Hire Date ' & date(HireDate,'YYYY-MMM-DD')
,SalesPerson)
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.
=aggr(
only({1}
Customer
)
,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.
=aggr(
only({1}Customer )
,OrderID)
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.
=aggr(
only({1} CustomerAndCountry)
,Customer)
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.
=aggr(
only({1<OrderID={"=sum({1}OrderAmount)>50000"}>}Customer )
,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 .
-Rob
