article Examples of Advanced Search Filters in Member Articles https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/ta-p/1594779 <P>From NPrinting June 2019</P><UL><LI>Introduction of a new filter type called <A href="https://help.qlik.com/en-US/nprinting/Content/NPrinting/ReportsDevelopment/Static-dynamic-filters.htm" target="_self"><EM><STRONG>Advanced search</STRONG></EM></A>. Here are a few examples of how it can be used.</LI></UL><P><EM><STRONG>Note:&nbsp;</STRONG></EM>I have made a few changes to a copy of the sample Qlik Sense App found <A href="https://help.qlik.com/en-US/nprinting/Content/NPrinting/Examples/Examples.htm" target="_self">here</A> and used that as the Connection source for these examples. The modified App and exported report are attached to this post.&nbsp;&nbsp;</P><P><FONT color="#000000">Fields that exist in the source document that we use:</FONT></P><OL><LI><FONT size="2" color="#333399"><STRONG><FONT face="arial,helvetica,sans-serif">Date</FONT></STRONG></FONT></LI><LI><STRONG><FONT face="arial,helvetica,sans-serif" size="2" color="#333399">Customer</FONT></STRONG></LI></OL><P><FONT color="#000000">Variables that exist in the source document that we use:</FONT></P><OL><LI><FONT size="2" color="#333399"><STRONG><FONT face="arial,helvetica,sans-serif">vStartDate</FONT></STRONG></FONT></LI><LI><STRONG><FONT face="arial,helvetica,sans-serif" size="2" color="#333399">vEndDate</FONT></STRONG></LI></OL><P><STRONG><FONT size="4">Example 1.</FONT></STRONG></P><P><U>Filtering for a date range.&nbsp;</U></P><P>With Advanced search filters you can filter for a date range in any of these ways:</P><UL><LI>hard coding the <EM>From</EM> and <EM>To</EM> dates. Example: <STRONG><FONT face="courier new,courier" size="2" color="#333399">=Date &lt;= Date#('06/22/2019', 'MM/DD/YYYY') and Date&gt;=Date#('06/22/2018', 'MM/DD/YYYY')</FONT></STRONG></LI><LI>Using expressions. Example: <STRONG><FONT face="courier new,courier" size="2" color="#333399">=Date &lt;= num(Today(1)) and Date &gt;=num(Today() - 365)</FONT></STRONG></LI><LI>Using variables that exist in the source document. Example:&nbsp; <STRONG><FONT face="courier new,courier" size="2" color="#333399">=Date&gt;=$(vStartDate) and Date&lt;=$(vEndDate)</FONT></STRONG></LI></UL><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="FIG1.png" style="width: 536px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/14209iB0AFD49825185FE8/image-size/large?v=v2&amp;px=999" role="button" title="FIG1.png" alt="FIG1.png" /></span></P><P><EM><STRONG>Note:</STRONG> </EM>The above filters were defined on the&nbsp;<FONT size="2" color="#333399"><STRONG><FONT face="arial,helvetica,sans-serif">Date&nbsp;</FONT></STRONG></FONT>field. You can also define the same filters for other fields just as you can use expression search in the source document&nbsp;<A href="https://help.qlik.com/en-US/sense/Subsystems/Hub/Content/Sense_Hub/Selections/SelectionsToolbar/search-selections.htm" target="_self">if you want to search for values in associated fields</A>. For example you could use the exact same filter definition for the&nbsp;<STRONG><FONT face="arial,helvetica,sans-serif" size="2" color="#333399">Customer&nbsp;</FONT></STRONG>field to get different results:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="FIG2.png" style="width: 465px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/14210i90CA5D0D3EC1A024/image-size/large?v=v2&amp;px=999" role="button" title="FIG2.png" alt="FIG2.png" /></span></P><P>Screenshots of the Filter editor showing the difference:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="FIG7.png" style="width: 541px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/14222i6E315184CB25D20C/image-size/large?v=v2&amp;px=999" role="button" title="FIG7.png" alt="FIG7.png" /></span></P><P><STRONG><FONT size="4">Example 2.</FONT></STRONG></P><P><U>Filtering for the Top <EM>n</EM>&nbsp;</U></P><P>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.</P><P><STRONG><FONT face="courier new,courier" size="2" color="#333399">=Customer=aggr(if(rank(sum(Sales)) &lt;=10, Customer), Customer)</FONT></STRONG></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="FIG3.png" style="width: 528px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/14218iCB3E953D60F92EFA/image-size/large?v=v2&amp;px=999" role="button" title="FIG3.png" alt="FIG3.png" /></span></P><P><STRONG><FONT size="4">Example 3.</FONT></STRONG></P><P><U>Filtering for a range</U></P><P>Similar to example 1. Here again you can use variables or expressions instead of hard-coding the numbers.</P><P><STRONG><FONT face="courier new,courier" size="2" color="#333399">=sum(Sales)&gt;=300000 and sum(Sales)&lt;=500000</FONT></STRONG></P><P><STRONG><FONT face="courier new,courier" size="2" color="#333399"><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="FIG4.png" style="width: 524px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/14219iB5C432C941B55A46/image-size/large?v=v2&amp;px=999" role="button" title="FIG4.png" alt="FIG4.png" /></span></FONT></STRONG></P><P>Example using aggr() to get Customers with Sales above average but also less than or equal to $160,000</P><P><STRONG><FONT face="courier new,courier" size="2" color="#333399">=sum(Sales)&gt;Avg(TOTAL Aggr(Sum(Sales),Customer)) and sum(Sales)&lt;=160000</FONT></STRONG></P><P><STRONG><FONT face="courier new,courier" size="2" color="#333399"><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="FIG5.png" style="width: 605px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/14220iF591C0A5BDDAE461/image-size/large?v=v2&amp;px=999" role="button" title="FIG5.png" alt="FIG5.png" /></span></FONT></STRONG></P><P><STRONG><FONT size="4">Example 4.</FONT></STRONG></P><P><U>Filtering using wildcards</U></P><P>Advance search filters supports the use of wildcards. This example filters for all Customer names that begin with&nbsp; IB or end with EX</P><P><STRONG><FONT face="courier new,courier" size="2" color="#333399">=Customer like 'IB*' or Customer like '*EX'</FONT></STRONG></P><P><STRONG><FONT face="courier new,courier" size="2" color="#333399"><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="FIG6.png" style="width: 522px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/14221i041A32DDEABD667F/image-size/large?v=v2&amp;px=999" role="button" title="FIG6.png" alt="FIG6.png" /></span></FONT></STRONG></P><P>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.</P><P>- Daniel.</P><P><FONT size="2">To set up the example project:</FONT></P><UL><LI><FONT size="2">Extract the contents of <EM>AdvSearchExamples.zip</EM></FONT></LI><LI><FONT size="2">Create a Connection to the Qlik Sense App</FONT></LI><LI><FONT size="2">Create the filters as listed in Filter <EM>Definitions.pdf</EM></FONT></LI><LI><FONT size="2">Import the report. Instructions <A href="https://help.qlik.com/en-US/nprinting/Content/NPrinting/AdministeringQVNprinting/Import-export-report-from-different-NP-environments.htm" target="_self">here</A>&nbsp;</FONT></LI></UL> Fri, 18 Jun 2021 14:33:09 GMT Daniel_Jenkins 2021-06-18T14:33:09Z Examples of Advanced Search Filters https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/ta-p/1594779 <P>NPrinting June 2019 introduces a new filter type called <A href="https://help.qlik.com/en-US/nprinting/June2019/Content/NPrinting/ReportsDevelopment/Static-dynamic-filters.htm" target="_self"><EM><STRONG>Advanced search</STRONG></EM></A>. Here are a few examples of how it can be used.</P> Fri, 18 Jun 2021 14:33:09 GMT https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/ta-p/1594779 Daniel_Jenkins 2021-06-18T14:33:09Z Re: Examples of Advanced Search Filters https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1595926#M32 <P>Excellent!</P> <P>Thank you Daniel!</P> Tue, 25 Jun 2019 21:55:39 GMT https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1595926#M32 Frank_S 2019-06-25T21:55:39Z Re: Examples of Advanced Search Filters https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1617847#M33 <P>Very concise and easy to follow.</P><P>&nbsp;Thank you!</P> Wed, 28 Aug 2019 18:33:33 GMT https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1617847#M33 kourosh_r 2019-08-28T18:33:33Z Re: Examples of Advanced Search Filters https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1630267#M34 <P>Great tips Daniel...thanks! Do you know if there is a way to use the Advanced Filter to exclude Null values? Thanks.</P> Tue, 01 Oct 2019 22:14:21 GMT https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1630267#M34 tomdon14 2019-10-01T22:14:21Z Re: Examples of Advanced Search Filters https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1816090#M35 <P>This post seems to be at least partially out dated with current NPrinting version (May 2021).<BR />Search string like "<STRONG><FONT face="courier new,courier" size="2" color="#333399">=Date&gt;=$(vStartDate) and Date&lt;=$(vEndDate)</FONT></STRONG>" returns error; instead correct formatting is "<STRONG><FONT face="courier new,courier" size="2" color="#333399">&gt;=$(vStartDate) &lt;=$(vEndDate)</FONT></STRONG>".</P><P>Qlik has a youtube videon on advanced filtering to start with here&nbsp;<A href="https://youtu.be/8OxJpDSXkAk" target="_blank">https://youtu.be/8OxJpDSXkAk</A>&nbsp;</P> Fri, 18 Jun 2021 13:16:12 GMT https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1816090#M35 samuli_attido 2021-06-18T13:16:12Z Re: Examples of Advanced Search Filters https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1819398#M36 <P><a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/14647">@samuli_attido</a>&nbsp;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 &gt;= (vStartDate) and Date &lt;=(vEndDate)" works. Instead the version without the Date field is not working.</P> Fri, 02 Jul 2021 14:43:45 GMT https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1819398#M36 Ruggero_Piccoli 2021-07-02T14:43:45Z Re: Examples of Advanced Search Filters https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1924842#M37 <P><a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/48708">@Daniel_Jenkins</a>&nbsp;<a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/48729">@Ruggero_Piccoli</a>&nbsp;<a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/14647">@samuli_attido</a>&nbsp;@</P> <P>Can you please help with this below request</P> <P><A href="https://community.qlik.com/t5/Qlik-NPrinting-Discussions/NPrinting-Pixel-Perfect-Report-Date-Filter-Condition/m-p/1924841#M35889" target="_blank" rel="noopener">https://community.qlik.com/t5/Qlik-NPrinting-Discussions/NPrinting-Pixel-Perfect-Report-Date-Filter-Condition/m-p/1924841#M35889</A></P> Sat, 30 Apr 2022 03:05:36 GMT https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1924842#M37 rtaraka 2022-04-30T03:05:36Z Re: Examples of Advanced Search Filters https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1925052#M38 <P>Hi&nbsp;<a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/90061">@rtaraka</a>&nbsp;,</P> <P><a href="https://community.qlik.com/t5/user/viewprofilepage/user-id/48708">@Daniel_Jenkins</a>&nbsp;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.</P> <P>Best Regards,</P> <P>Ruggero</P> Mon, 02 May 2022 09:05:46 GMT https://community.qlik.com/t5/Member-Articles/Examples-of-Advanced-Search-Filters/tac-p/1925052#M38 Ruggero_Piccoli 2022-05-02T09:05:46Z