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]

Hello Mr. Troyansky,
@Oleg_Troyansky 

unfortunately it didnt work. 
what im sure about is that ignoring filters do, it ignores the filters passed in the set modifier ( in my example Channel and Year),
and thus the $ which is the set identifier for this expression  will not be affected by both , Year or Channel, 

Sum(
{$<Channel, Year,

[Invoice Date] = {"=Year([Invoice Date]) = $(=max(year({1} [Invoice Date])-1))"} >}
[# Amount])
example : 
if now on the dashboard i filter Year = 2014 with a filter pane visual
the set identifier in the expression (which is the $) should not be affected by the filter and thus the set identifier should contain data for both years (2013,2014) (assuming that the whole data set refers to data from 2013 and 2014 ) and then the set modifier should manipulate the records of the set identifier, and return the appropriate values (so it should returns the amount for the year 2013).

Using the method above, this whole concept is not working properly.

i know there is different ways to fix the issue by using : 

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

or

method 3

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

but im furstrated for the reason why 'ignoring filter' in the first method isnt working properly.

[Oleg_Troyansky]

@hic , it looks like @Daniel29195  might be onto something here. I tested this scenario, and I'm seeing some bizarre behavior. Examine the screenshot above, with various similar expressions that produce different results.

In most of them, the goal was to show total Amount for dates in year 2013, while ignoring user selections in the fields Channel and Year (the data set contains data from years 2013 and 2014 - it's from my book, which you know intimately, as a technical editor 🙂 

Here is the strange behavior that I'm seeing:

• Ignoring selections in Channel and Year works perfectly with no other conditions applied to the field Date (column 4)
• Ignoring these selections and selecting Dates in 2013 works well when I select the _PYTD_Flag={1} (column 7) and when I'm applying a simple numeric search to the field Date (column 8  )
• The Year selection doesn't seem to get ignored when any form of advanced search is applied to the field Date:
  • Comparing Year(Date) with the calculated Max Year - 1 (Columns 1 and 5)
  • Explicitly comparing Year(Date) with a number 2013
  • Selecting dates using Advanced Search, that compares the _PYTD_FLag to 1 (column 6)
  •  

Is there something that we are missing, or could it be a bug, related to advanced search, in combination with ignoring selections in fields? I'm happy to share my sample app if you are interested.

[hic]

Oleg, Daniel

I have taken a look at it, and I cannot see anything wrong... Maybe I'm missing something, and if so, please point it out.

1) Clearing a selection in the set expression does NOT affect calculations inside the set expression. Calculations are always based on the context, normally current selection.
So, in the following calculation, clearing the Year will not affect the condition on Date.
{$<Channel, Year, Date = {"=Year(Date) = $(=Max(Year(Date)-1))"} >}
Instead, the Max(Year(Date)) is calculated from the current selection.

2) An expression search is a hypercube with the selection field as dimension, so there will be a separate calculation on each "row", based on current selection.
So, in the following calculation, there will be a separate calculation for each date.
{$<Date = {"=Year(Date) = $(=Max(Year(Date)-1))"} >}
Hence, the Year(Date) will return different values on different rows. But the dollar expansion is made globally and doesn't care about the rows in the hypercube.

This means that you can get situations where the year to the left of the equals sign always is different from the year to the right of the equals sign (as Oleg points out in one of the answers).

3) A numeric search will make one calculation (usually based on current selection), and directly pick out the correct values.
{$<Date = {$(=max(year(Date)-1))} >}

4) A function call to the left of the assignment operator will create a field-on-the-fly, whereas a function call to the right is an aggregation.
So, Year(Date) is very different in the two cases below:
{$<"=Year(Date)" = {2018} >}
{$<Date = {"=Year(Date) = $(=Max(Year(Date)-1))"} >}

HIC

[Daniel29195]

---- Comment deleted. ----

[Oleg_Troyansky]

@hic

Thank you for your input. What I believe to be wrong in the observed behavior, which you can see on the screenshot that I posted, is the following:

When a Set Analysis filter limits the dates to year 2013 and at the same time the user selects Year=2014, that should produce an empty set, hence all the numbers are showing zeroes, as expected. 

When we want to protect the calculation from this situation, we list the calendar fields that need to be disregarded by Set Analysis - Year, Quarter, Month, etc. In these cases, the combination of Set Analysis selecting Dates from 2013, with the User Selection of Year=2014, should still produce numbers for 2013, because the user selections in the field Year should be disregarded. That is not happening when Advanced Search is used. Let me reiterate these scenarios in my example:

• Disregarding selected Year works with no additional conditions.
• It works when I use Prior YTD flag = {1} condition
• It works when the Date field is being limited by simple numeric search, such as {"<41640")
• However, disregarding the Year selections doesn't work with any form of Advanced Search on the field Date:
  • It doesn't work with the advanced search conditions based on the PYTD Flag
  • It doesn't work with the condition that states Year(Date) = Max Year - 1
  • It doesn't work with the condition that states Year(Date) = 2013

In the last 3 examples, the chart shows zeroes when Year 2014 is selected, while it's supposed to show the totals for 2013, since the year selection should be ignored by the Set Analysis modifier. I believe that this is either a bug or an unexpected behavior that deserves a better explanation.

I can't understand why selecting _PYTD_Flag={1} produces the expected results, and selecting Dates with the same condition listed as Advanced Search, doesn't. If this is how it's supposed to work, then it needs to be explained further. IMHO...

[Oleg_Troyansky]

@Daniel29195 

I forgot to mention it before, but there is a syntax error in your formula that makes it produce different results than it should:

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

The Set Analysis {1} belongs in the Max() function, not in the Year() function. Try the same calculations with the {1} in the Max() function and see if you get different results:

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

[Daniel29195]

@Oleg_Troyansky 
i noticed the error when it was sended previously, and i wrote the {1} in the max function not in the year function, but didnt work. 

[Oleg_Troyansky]

@Daniel29195 I don't understand what you mean by "didn't work". Here is how it looks on my end:

For dates in 2014, the condition returns FALSE (0), and for dates in 2013, the condition returns TRUE (-1).

[Daniel29195]

this is what i m getting, 
column 3 is the expression where the ignore filter concept works as it should, and column 2 is the expression that wasnt ignoring the filters. @Oleg_Troyansky 

[Oleg_Troyansky]

@Daniel29195 - yes, when Year 2014 is selected, I'm getting the same results, but I still can't explain them, based on my understanding of Set Analysis. Clearly, it's not working as we expect it to work, whether it's a bug in the product, or our incorrect understanding of how it's supposed to work.

However, in your previous message where you showed a table with all FALSE results, I believe that some of those results should be TRUE when the {1} is placed correctly.