The Expression Search

[hic]

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.

[hic]

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.

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

[#3]

Nice post !

[#4]

very use full

Thank you for share it.

[kevinpintokpa]

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

[rwunderlich]

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?

[kevinpintokpa]

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!

[jchoucq]

Dear Henric,

very useful feature.

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

Best regards.

Johann

[hic]

No, this should work fine also in Qlik Sense.

HIC

[jchoucq]

absolutly, you're right, my fault !

Thanks a lot.

Johann

[Daniel29195]

Hello @hic ,

thank you for sharing your knowledge with us.

i wish if you coul help with this  ,

Method 1:
Sum(
{$<Channel, Year,

[Invoice Date] = {"=Year([Invoice Date]) = $(=max(year([Invoice Date])-1))"} >}
[# Amount])

Method 2 :
Sum(
{$
<
Channel,Year,
"=Year([Invoice Date])" = {$(=max(year([Invoice Date])-1))} >
}
[# Amount])

both are giving me the same result, however, the first one, the Ignoring Filter is not working properly , and i cant seem to find the reason. 

[Oleg_Troyansky]

@Daniel29195 - I believe the issue in your Method 1 is in the search condition for the Invoice Date.

That condition, evaluated at the Invoice Date level, is asking for the Year(Date)=Max(Year(Date)-1). At the Date level, it will never render True, because for each single Date, Year(Date) is always the same as Max(Year(Date)), and therefore it can never be equal to the Max(year(Date)-1). 

To fix this problem, you can add Set Analysis {1} to the Max() function, like this:

Sum(
{$<Channel, Year,

[Invoice Date] = {"=Year([Invoice Date]) = $(=max(year({1} [Invoice Date])-1))"} >}
[# Amount])

This should solve the problem

Join us at the Masters Summit for Qlik, the most advanced Qlik training event in the world, in New Orleans on Nov 14-16. You will learn advanced Set Analysis, AGGR(), data modeling, and many other advanced Qlik topics!