Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
- Date
- Customer
Variables that exist in the source document that we use:
- vStartDate
- 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)
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:
Screenshots of the Filter editor showing the difference:
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)
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
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
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'
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
