7 December 2022

Searching within selections

Share this message
Searching within selections

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about searching within selections:

Using search expressions in Qlik Sense. Filter data based on the output of an expression.

Sometimes you are put on the spot as a developer and have to come up with a quick solution. When that happens, experience and broad knowledge are vital tools to have. Knowing which possibilities there are for search selections is one of them:

The correct answer is D.

The expressions search

Using expressions within the selection pane in Qlik is a powerful and convenient way to quickly find and select specific data points. This can be especially useful when you are working with large datasets or complex visualizations, as it allows you to quickly and easily focus on the information that is most relevant to you.

The correct way to use the expressions search is to start with an equal sign as operator (=). In this question we want to select all customers with sales exceeding 10.000 units. We start with the equal sign and then the expression =Sum(Units)>10000 on the Customer field. This tells Qlik to evaluate the Customer field based on this aggregation and select all associated field values from Customer.

If we refer to the figure below, we can see that we have a total of 44 distinct customers, with 409.500 units sold in total.

Now we input the expression search into the Customer selection pane:

After pressing enter the selection is made. Resulting in the 17 customers we see in the figure below. This selection can now be added to a bookmark.

The nice thing of storing expression searches as a bookmark is that the bookmark is dynamic. As we can see in the bookmark itself:

There is a set expression on the Customer field, selecting all customers where the sum of units is bigger then 10.000.

To illustrate this, we add some sales to the dashboard for Bob Smith. As we see in the first figure above, Bob only sold 300 units. This is now changed to 30.000 units. After reloading the data and selecting the bookmark, we get the following result:

The expression search is most effective when used in fields associated to the value you are looking for. For example. You could also make a selection on the Units field:

By typing >10000 we do a numeric search of bigger than 10000 on the field Units. It immediately shows 30000 as the only possible value. After pressing enter to make the selection we end up with the following result:

Here we have our friend Bob as the only result, since he is the only one with a units amount more than 10000. Since there is no aggregation done, this is not the result we are looking for.

Overall, using expressions within the selection pane in Qlik is a powerful and convenient way to quickly and easily filter your data and focus on the information that is most relevant to you. Whether you are a data analyst, a business user, or just someone who wants to quickly explore their data, using expressions within the selection pane can help you get the most out of your Qlik apps.

Other search selection options

There are obviously more options to make search selections. For example, just entering information in a Name field being the most common. We will list the names here, but not discuss them in depth:

  • Text Search (Using text to search)
  • Fuzzy Search (Using the tilde (~) character to find inexact matches)
  • Numeric Search (Values, possible in combination with symbols like greater than, less then, etc etc)
  • Expression Search (See this blogpost)
  • Compound Search (Use search operators to combine searches)

Verifying the accuracy and relevance of results from expression searches in Qlik, particularly with large datasets or complex expressions, involves a few key strategies to ensure the data you’re working with is both accurate and relevant. Firstly, cross-referencing the results of the expression search with known data points or benchmarks can provide an immediate sense of accuracy. For example, if you’re using an expression to calculate total sales over a specific period, comparing the output with manually selecting the same data.

Another method is to incrementally build and test your expressions, especially when they’re complex. Start with a simpler version of the expression that you know the outcome for, and gradually add complexity. This step-by-step approach helps in isolating any discrepancies or errors in logic as you build up the expression.

Finally, peer review or collaborative validation can be incredibly useful. Sharing your findings and the expressions used with colleagues or other Qlik users can provide new perspectives and insights, helping to ensure that the results are not only accurate but also relevant to the intended analysis or business questions. Engaging with the Qlik community through forums or user groups can also be a valuable resource for validation and feedback.

Together, these strategies form a comprehensive approach to verifying the accuracy and relevance of expression search results in Qlik, ensuring that users can confidently rely on their data-driven insights.

The performance of using expression searches in Qlik can vary based on the complexity of the expressions and the size of the dataset. While Qlik is optimized for fast data retrieval and analysis, highly complex expressions or searches performed on very large datasets may impact performance. Qlik’s in-memory technology ensures that data is readily available for analysis, but as the complexity of the search increases, so does the computational overhead. It’s essential to design expressions efficiently and be mindful of the dataset’s size to mitigate potential performance issues. Qlik does provide tools and features to monitor and optimize app performance, which can be particularly useful in managing large datasets.

Qlik has robust features to manage data visibility and user permissions. When using expression searches, Qlik respects the security rules and access controls defined within the Qlik Management Console (QMC) or through Section Access within Qlik apps. This means that if a user attempts an expression search, the results will only include data that the user is authorized to see. Section Access is a powerful feature that can restrict data access at the row level, ensuring users can only see data relevant to their permissions. This security model ensures that expression searches do not inadvertently expose sensitive or restricted data to unauthorized users. It’s crucial for administrators to correctly configure these settings to maintain data security and compliance.

That’s it for this week!

Take your Qlik skills to the next level!

Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level.

Join the Bitmetric team!

Join the team!

Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening.

Expressions Friday Qlik Test Prep Search Solution

How can we help?

Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have.