“Indirect” Selections in QlikView

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.

Step 1: Search Based on an Expression 

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: 

  • =UnitPrice<CataloguePrice  – find orders where products were sold below the list price
  • =UnitPrice/CataloguePrice<0.90  – find orders discounted more than 10%
  • =Count(DISTINCT ProductID)<5  – search for customers who have ordered few products
     

This is a powerful search technique, but only power users are likely to use it. Here’s why:

  • QlikView does not help you with the syntax – you must type it correctly on your own.
  • Being an expression, the search string must start with the equal sign “=”. Omit the equal sign and you likely will find no matches.
  • You must thoroughly understand the data model in order to structure this kind of search correctly. 

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.

Step 2: Automate an Indirect Search 

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. 

Conclusion

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.

James Carpenter's photo
Principal Consultant
James is an expert in developing business requirements and creating business solutions using Qlik and other tools.

 

Contact Us

National Office Telephone | Mon-Fri 8:30am-5:30pm CT