Qlik Community

Qlik NPrinting Documents

Documents related to Qlik NPrinting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More

Examples of Advanced Search Filters

Support
Support

Examples of Advanced Search Filters

NPrinting 16.x allows the use of Advanced search in filters. Now NPrinting June 2019 gives us this functionality with the 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 
Attachments
Comments
Support
Support

Excellent!

Thank you Daniel!

0 Likes
kourosh_r
New Contributor III

Very concise and easy to follow.

 Thank you!

0 Likes
tomdon14
New Contributor II

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

0 Likes
Version history
Revision #:
8 of 8
Last update:
‎2019-06-25 05:54 PM
Updated by:
 
Contributors