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

[Daniel29195]

ok i got it now , @Oleg_Troyansky 

sorry for the misunderstanding from my part, 

the table that i created above,  is my understanding about how set analysis works,

in the table that i draw, i was like explaning how the advanced searches (base on my logic) is working  behind the scenes (in the  full expression) :

Sum(
{$<Channel, Year,

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

[Daniel29195]

@Oleg_Troyansky 
i think i understand now the reason of such behavior ( when filtering on year even though we are ignoring the filter ) ,
(for the purpose of explanation, (i took only 7 rows from the full data : 3 rows for 2013 and 4 rows from 2014)

this is the expression in question:
Sum( 
{$<Channel, Year,

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

first scenario : no filters : 

i used concat for the purpose oF DEBUGGING.

Sum({$<Year,[Invoice Date] = {"=$(=concat(Year([Invoice Date]),',')) = $(=max(year([Invoice Date])-1))"}> }[Sum([# Amount]])])

using the above expression, you will have : 

and the result of the expression : Sum({$<Year,[Invoice Date] = {"=Year([Invoice Date]) = $(=max(year([Invoice Date])-1))"}> }[Sum([# Amount]])]) , will be equal to 3.

scenario 2 : the user filters on year = 2014 

the advanced search at the right is evaluated in the context of the current selection ( as @hic mentioned in his input), so basically what the advanced search is evaluating is : year(invoices date in 2014) = max(year(invoices date in 2014)-1)  ==> 2014  = 2013 which will always return False.  
check the image below : 

im filtering on year = 2014

debugging image below : base on the concat function .

so 2014 is being compared with 2013, 4 times,
now it is true that we have 7 rows in our data, but since the part to the right ( in our case the advanced search is evaluated in the context of the current selection, ==> only 2014 should be visible in our concat function, ( and this is exactly what we got)
and for this, this expression :  

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

will result to 0

now for the set identifier part: ( still taking the filter Year = 2014 into consideration)

for the identifier, the ignoring filter (of the Year) is being applied correctly on the set identifier, and the set identifier does contain the data for both years 2013,2014.

this can be seen if you manipulate the debug expression to something like this : i
Sum({$<Year,[Invoice Date] = {"=$(=concat(  {1} Year([Invoice Date]),',')) = $(=max(year([Invoice Date])-1))"}> }[Sum([# Amount]])])

so now the evaluation in the advanced search is 2013 = 2013 , for the first 3, and 2014 = 2014 for the last 4  

now the important part : 

IF  the Year filters wasnt ignored by the ignoring filter concept, then the set identifier should not contain data for 2013, ( since the user is selecting year = 2014 from the filter pane), 
and the result should return  0 

HOWEVER , (since i cant use set analysis in the year function, so i enclosed the field in Only, so i can apply the set identifier),

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

you can also write  : {<Year>} instead of {1}  .

and the result will now be 3 ( as it should ) : ( keeping in mind that the user is still selecting from the filter pane : year = 2014)

from this you can tell, that if the ignoring filter concept wasnt applied correctly, then, the result would be 0 not 3 . (check below (column 3 ) 

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

using the expression above, ( wihtout ignoring the Year filter selected by the user)  :

@Oleg_Troyansky 

[Oleg_Troyansky]

@Daniel29195 - yes, I came to the same conclusion. As confusing as it looks, the issue is in the Advanced Search, or Search with Expression, as it's called now. And the solution is indeed using the explicit only() function with the Set Analysis that ignores all the fields that need to be ignored.

I'm going to write a blog article about it, stay tuned.

Thank you for sparking this conversation! 

[barnabyd]

G'day @Daniel29195 & @Oleg_Troyansky,

I didn't read through the issue above in detail so I don't really understand the problem, but one thing that I would suggest to simplify the situation is to create flags in the load script for 'This Year' and 'Last Year'.

So this code: [Invoice Date] = {"=Year([Invoice Date]) = $(=max(year( [Invoice Date])-1))"} 

Becomes much simpler: [Invoice Last Year Flag] = {1}

If your data set is large, then this also can speed up the response times hugely. It might even side step your problem above.

I hope this is a useful comment.

Cheers, Barnaby.

[Daniel29195]

Hello @barnabyd , 

( Im not a Qlik expert to be honest ) but i suppose that the solution you have provided will  work   , and many alternatives can be used to get the needed result 

But the main core of the discussion was about how advanced search expression works (behind the scene) 

If you are interested, check this article written by Mr. @Oleg_Troyansky   one of the Qlik  pioneers,  explaning the advanced search behind the scenes in much details .

https://www.naturalsynergies.com/q-tip-24-the-only-solution-or-the-subtle-difference-between-simple-...

[barnabyd]

Thanks for the link @Daniel29195, and thanks for the article @Oleg_Troyansky,

I now understand the problem that you're working through. I note that column 3 in the example table was a version of my suggestion above and it's nice to know that it doesn't suffer from this issue. Having said that, I'm always keen to learn more about the power of Set Analysis. Even though I have been using Qlik since 2007, I have never fully understood all of its powers and possibilities.

Cheers, Barnaby.