Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

The Expression Search

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

18 Comments

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
145 Views
Employee
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
145 Views

Thanks Henric.

0 Likes
145 Views
datanibbler
Esteemed Contributor

Great post!

0 Likes
145 Views

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

145 Views
joaquinlr
Valued Contributor II

Hi hic :

What a intersenting search method !!!!

Thanks for share it

Joaquín

0 Likes
145 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
145 Views
Employee
Employee

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

HIC

145 Views
MVP
MVP

Great post, Henric. I really like this approach:

=Sum(Product='Trousers')

0 Likes
145 Views
Not applicable

very informative , thanks for sharing

0 Likes
145 Views
Not applicable

Nice post !

0 Likes
145 Views
Not applicable

very use full

Thank you for share it.

0 Likes
145 Views
kevinpintokpa
Contributor II

This is a very powerful feature - thanks for sharing and writing about it! hic

BUT:  It doesn't seem to work with Count() functions. I have a field named Key in my data table and suspected that it had duplicates.  So I tried the following:

  • Created a list box for a different field A in the same table.
  • Used the search expression: =Count ( Distinct Key ) > 1

The result was odd.  Many values of A were selected, but they definitely included rows where Count ( Distinct Key ) was equal to 1, not what I wanted.

In contrast, I tried =Sum () and it seemed to work properly.

I've attached a QVW below that illustrates both cases.

Test Expression Search.qvw

0 Likes
145 Views

In your uploaded example, it behaves as I would expect.

=Count(Key)>1 in the Name list selects nothing, because there is no Name with more than one key.

=Sum(Key)>3 selects those Names that have a Key value greater than 3.

Did you get different results?

145 Views
kevinpintokpa
Contributor II

You're right, it actually does work   I had a misunderstanding of my original data.  When I went back and looked at the model more carefully, it worked properly.  So false alarm!

0 Likes
145 Views
jchoucq
Contributor II

Dear Henric,

very useful feature.

It seems that it is not possible in Qliksense. Do you confirm ?

Best regards.

Johann

0 Likes
145 Views
Employee
Employee

No, this should work fine also in Qlik Sense.

HIC

Expression search.png

0 Likes
145 Views
jchoucq
Contributor II

absolutly, you're right, my fault !

Thanks a lot.

Johann

0 Likes
145 Views