Answer Ad-Hoc Questions With Advanced Search Expressions in Qlik

In the latest Ometis blog, Chris Lofthouse shows you how to answer ad-hoc questions with Advanced Search Expressions in Qlik. 

Have you ever been asked an ad-hoc question that needs answering immediately but it’s just a one-off piece of analysis, so there is no scope to build a dedicated visual or dashboard to answer it? Well, here is one solution that may work for you and it doesn’t require a professional licence to use it – Advanced search expressions.

What are Advanced search expressions in Qlik?

Advanced search expressions are the ability to enter an expression into a search box of a field in order to filter the data set. As a result, they are a fast and efficient way of handing those once-in-a-while questions with minimal investment.

Let’s look at an example:

Example question: Can you provide me with a list of locations that have revenue greater than one million?

To use Advanced search expressions is simple, toggle the search box like you would normally.

• In this example, I’ve clicked the Selection tool button in the top-right of the screen to access my fields:

•  Next, click towards to the top of a field to activate the object, and type the character ‘=’ (equals). This toggles the search box and an Advanced search expression must start with an equals sign.

• We can now type the expression to answer our question e.g. =SUM([Net Price])>1000000

• Qlik Sense will instantly return the values that meet this criteria,  all that is left to do is press the return key (Enter) to select the values.

It’s worth noting we can extend expressions using AND/OR logic e.g. =SUM([Net Price])>1000000 AND SUM([Net Price])<5000000

I guess the limitation you may be thinking about so far is that this relies on your expressions being relatively simple, otherwise writing those expressions would be overwhelming for the average business user. However, you may now have realised that I have a trick up my sleeve…Variables!

Did you know you can use variables in Advanced expressions searches?

For example, let’s answer the question “In which cities are we making a loss?”. In this example I will use the expression:

=$(vCost)>$(vTotalSales)

Both vCost and vTotalSales are variables I have previously created, each containing an expression definition.

Bingo! Qlik evaluates the expression and returns the cities, in this case, that meet the search criteria. Personally, I think that’s a must-know for all Qlik power users who want to leverage the most out of their Qlik solution.

Let us know your thoughts in the comments section below, and/or on Twitter, LinkedIn or YouTube.

Topic: Data analytics