Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

In my last blog post I wrote about the overall logic in the search string. Today I will dive into one specific search method: the Expression search.

Any search string beginning with an equals sign will be interpreted as an expression search, e.g.:

 

     =Sum(Sales)>80000

 

If you use this search string in a field listing customers, it will pick out customers for which the total sales number is more than 80000. This means that the Qlik engine will create a hypercube – the same as what you have in a chart – with Customer as dimension and Sum(Sales)>80000 as measure, and use this to determine the search result.

Image1b.png

 

You can of course use the same search string in any field, e.g. Products or Months. But the results will be different, just as the different charts would show different numbers.

 

There are many implications of the above; implications you need to be aware of in order to make correct use of an expression search.

 

First of all: A hypercube is normally based on the current selection, which means that the search will not include excluded values. This is different from the wildcard search and the numeric search where the search is made in the symbol tables that hold all values, also excluded ones. If you want to include excluded values in your search, you need to use Set Analysis in your search string, e.g.:

 

     =Sum({1} Sales)>80000

 

Further, the search string is a Boolean expression. This means that you can have several criteria, e.g.:

 

     =Sum(Sales)>80000 or Count(distinct OrderID)>100

 

But it also means that you can simplify your expression, using the fact that all non-zero numbers are interpreted as TRUE. As an example, the two following expressions are equal:

 

     =Count(If(Product='Trousers', OrderID))>0
     =Count(If(Product='Trousers', OrderID))

 

Finally, the expression search implies an aggregation. This means that you must use an aggregation function. If you don’t, the Only() function will be used. For example, if you want to search for customers from a specific country, you can write

 

     =Country='Germany'

 

This will work fine if each customer is associated with one country only. However, it will not work if there are several possible values per customer. For example, if you instead want to search for customers that have bought a specific product, you might try:

 

     =Product='Trousers'

 

Then you will find some customers, but probably not the ones you are looking for. You will find those that have bought only trousers and nothing else, since it is interpreted as Only(Product)='Trousers'. If you want to find all customers that have bought trousers and perhaps also other products, you should instead try one of the following

 

     =Count(If(Product='Trousers', OrderID))>0
     =Count({$<Product={'Trousers'}>} OrderID)
     =Index(Concat(distinct Product, ','), 'Trousers')
     =Sum(Product='Trousers')

 

I admit that neither of the above expressions is very user-friendly, but it is nevertheless how it works.

 

If you want a simpler way to find these customers, you will just have to do it the manual way: Click on 'Trousers' in the Product list box, then look at the list of customers.

 

HIC

 

Further reading related to this topic:

The Search String

Symbol Tables and Bit-Stuffed Pointers

The Only Function

It’s all Aggregations

36 Comments
Colin-Albert

Hi Henric,

Can you clarify that your Sum(Sales) field is configured with a point as the thousands separator, as the example may be confusing to users who are used to seeing a point as the decimal separator !

So on your example 80000 would display as 80.000  rather than 80,000

Life would be so much easier if there was one standard for number and date formats!

0 Likes
7,259 Views
hic
Former Employee
Former Employee

Sorry. Didn't think about that. But you are right - it would be simpler with common number and date formats. Anyway, the picture has been replaced now.

HIC

0 Likes
7,259 Views
Colin-Albert

Thanks Henric.

0 Likes
7,259 Views
datanibbler
Champion
Champion

Great post!

0 Likes
7,259 Views
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Great post Henric. Regarding your last point as to manually finding Customers who purchased Trousers, I would think using Associative Search is a simple solution as well.

-Rob

7,259 Views
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

Hi hic :

What a intersenting search method !!!!

Thanks for share it

Joaquín

0 Likes
7,259 Views
Not applicable

Thank you Henric!

I'm new to Qlik Sense and  I nerver miss your Blog articles. It really help me understand the logic behind the Qlik products.

Last weekend I've been fighting with my expression to highlight the maximum Quantity amount as showed here:


  

I would like the Brazil Quantity 3,358,230 to be in green.

I used different expressions:

=if((Max(Quantity), Quantity rgb(0,255,0), Quantity)

=IF(Sum( {$<Quantity={"<=$(=Max(Quantity))"}>} Quantity),rgb(0,255,0)

=firstsortedvalue( Quantity, -Aggr(Max([Quantity]),Quantity,rgb(0,255,0)))

Unfortunately none is working! Again maybe my expressions are totally wrong.

Thanks for your advise and insight Henric!

0 Likes
6,045 Views
hic
Former Employee
Former Employee

=If(Sum(Quantity)=Max(total Aggr(Sum(Quantity),pays,MonthYear)),LightGreen())

HIC

6,045 Views
rbecher
MVP
MVP

Great post, Henric. I really like this approach:

=Sum(Product='Trousers')

0 Likes
6,045 Views
Not applicable

very informative , thanks for sharing

0 Likes
6,045 Views