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

Examples of Advanced Search Filters

cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel_Jenkins
Specialist III
Specialist III

Examples of Advanced Search Filters

Last Update:

Jun 18, 2021 10:33:09 AM

Updated By:

Frank_S

Created date:

Jun 25, 2019 5:54:42 PM

Attachments

From NPrinting June 2019

  • Introduction of a new filter type called Advanced search. Here are a few examples of how it can be used.

Note: I have made a few changes to a copy of the sample Qlik Sense App found here and used that as the Connection source for these examples. The modified App and exported report are attached to this post.  

Fields that exist in the source document that we use:

  1. Date
  2. Customer

Variables that exist in the source document that we use:

  1. vStartDate
  2. vEndDate

Example 1.

Filtering for a date range. 

With Advanced search filters you can filter for a date range in any of these ways:

  • hard coding the From and To dates. Example: =Date <= Date#('06/22/2019', 'MM/DD/YYYY') and Date>=Date#('06/22/2018', 'MM/DD/YYYY')
  • Using expressions. Example: =Date <= num(Today(1)) and Date >=num(Today() - 365)
  • Using variables that exist in the source document. Example:  =Date>=$(vStartDate) and Date<=$(vEndDate)

FIG1.png

Note: The above filters were defined on the Date field. You can also define the same filters for other fields just as you can use expression search in the source document if you want to search for values in associated fields. For example you could use the exact same filter definition for the Customer field to get different results:

FIG2.png

Screenshots of the Filter editor showing the difference:

FIG7.png

Example 2.

Filtering for the Top n 

Here is an example using Advanced search filters and Aggr() to filter for the Top 10 Customers (most Sales). You can of course use a variable or an expression that results in an integer instead of hard-coding the number 10.

=Customer=aggr(if(rank(sum(Sales)) <=10, Customer), Customer)

FIG3.png

Example 3.

Filtering for a range

Similar to example 1. Here again you can use variables or expressions instead of hard-coding the numbers.

=sum(Sales)>=300000 and sum(Sales)<=500000

FIG4.png

Example using aggr() to get Customers with Sales above average but also less than or equal to $160,000

=sum(Sales)>Avg(TOTAL Aggr(Sum(Sales),Customer)) and sum(Sales)<=160000

FIG5.png

Example 4.

Filtering using wildcards

Advance search filters supports the use of wildcards. This example filters for all Customer names that begin with  IB or end with EX

=Customer like 'IB*' or Customer like '*EX'

FIG6.png

I'm very excited to see the myriad ways this powerful feature will be used going forward. I hope this short introduction gets you started.

- Daniel.

To set up the example project:

  • Extract the contents of AdvSearchExamples.zip
  • Create a Connection to the Qlik Sense App
  • Create the filters as listed in Filter Definitions.pdf
  • Import the report. Instructions here 
Labels (2)
Comments
Frank_S
Support
Support

Excellent!

Thank you Daniel!

kourosh_r
Contributor III
Contributor III

Very concise and easy to follow.

 Thank you!

tomdon14
Contributor III
Contributor III

Great tips Daniel...thanks! Do you know if there is a way to use the Advanced Filter to exclude Null values? Thanks.

samuli_attido
Partner - Contributor II
Partner - Contributor II

This post seems to be at least partially out dated with current NPrinting version (May 2021).
Search string like "=Date>=$(vStartDate) and Date<=$(vEndDate)" returns error; instead correct formatting is ">=$(vStartDate) <=$(vEndDate)".

Qlik has a youtube videon on advanced filtering to start with here https://youtu.be/8OxJpDSXkAk 

Ruggero_Piccoli
Support
Support

@samuli_attido I suggest you to test the formula in Qlik Sense and use it in Qlik NPrinting only after be sure it works. I tested in my installation and the following: " =Date >= (vStartDate) and Date <=(vEndDate)" works. Instead the version without the Date field is not working.

Ruggero_Piccoli
Support
Support

Hi @rtaraka ,

@Daniel_Jenkins doesn't work any more in the company. Please also avoid to cross posting with links not on topic. Thanks. It is better to ping us directly in the question where you need help.

Best Regards,

Ruggero

Version history
Last update:
‎2021-06-18 10:33 AM
Updated by: