Sometimes you need to approach your data from a different direction. In this post you’ll learn how to find and select values in your QlikView application based on other associated values in the data – an indirect selection.
Sometimes you need to approach your data from a different direction. In this post you’ll learn how to find and select values in your QlikView application based on other associated values in the data – an indirect selection. The post’s examples are based on a QlikView application, but most of the concepts also apply to Qlik Sense.
Normally when making selections to filter data in a QlikView application, you click specific values in some field. Then QlikView’s associative logic updates the metrics display to reflect your selection. The example below shows selecting OrderID values and the result.
But suppose your requirements included this: “The users must be able to find orders whose total sales amount is greater than 10,000 or some other value that the user specifies. They must also be able to search for customers having total sales over a threshold amount.” Using indirect searching and some simple automation, you can implement both of these requirements.
QlikView supports searching in a field using an expression that aggregates some other field or compares fields.
In the example below, the user has activated the OrderID search box and typed an expression to find orders whose total sales amount is over 10,000. Remember that a QlikView expression starts with the equal sign, “=”.
The full search string is =Sum(LineSalesAmount)>10000.
Be sure to remove the wildcard * characters from the search box if QlikView inserted them automatically.
After the user types the search string and presses Enter, the only orders shown are those whose detail rows total to more than 10,000. QlikView is not filtering for order detail lines with amounts greater than this threshold; rather, it is filtering on the total amount aggregated over each OrderID. This is similar to SQL’s HAVING clause in a GROUP BY query.
Here are other examples of search-string expressions:
This is a powerful search technique, but only power users are likely to use it. Here’s why:
Use this method to perform ad hoc searches, or to test your search logic as a developer before automating it. That leads us to the next step.
Automating this kind of search gives less experienced users the same power and flexibility to analyze their data.
In the example below, an action button lets the user perform an indirect search with one click. The button uses the Select in Field action, whose two parameters are a field to search and the search string to use. Notice the syntax of the Search String parameter.
This is a simple automation example where the search value is hard-coded. Other options include placing the search amount in a variable tied to a slider object or input box, saving the search as a bookmark, or adding the action to a gauge chart or other object rather than a button.
QlikView provides several ways to search for data based on associated values in the application data model. Set analysis and chart dimension limits are two other methods for implementing this kind of search. But the “indirect” search gives the expert user a powerful tool for ad hoc analysis, and it offers the application developer a flexible way to help users interact with their data. And it’s all about the user, after all.