Filtering Data

Intro

Filters are most certainly a major part of your report design and the way filtering in ancoreShare Server works make it a very powerful and highly flexible tool. Filtering means selecting only certain parts of your data to be displayed or calculated in in charts and tables as well as in text elements.

Use filters to build security walls by restricting access to data for report recipients. This way setting filters create security for sensitive data.

Reduce and focus the data from your Qlik App source with filters to ensure that the report results contain only relevant data that answers the specific business queries for which the report is created. And you can do all this dynamically with Qlik expressions to tailor your design i.e., around dates, recipients, KPIs, or any variables within your Qlik App source.

Understanding the basics

Filtering vs. selecting

When filtering in ancoreShare, there is one important difference to making selections in a Qlik App: Locking.

Filtering is literally reducing data more and more, and records that have been excluded by a filter previously are no longer available for further filtering. Technically it is like making a selection in Qlik and then locking it before making the next selection and so on. 

This ensures high security when working with data.

In Qlik's world of "green, white, and grey data" this means that after setting a filter only "white data" is available for the next filter (and of course "green data"), but not any "grey data".
For example, when you select the product line "Drink", you exclude the product sub group "Bagels" definitively (see image below), but you could further filter "Milk" and "Coffee".
This also excludes the product line "Food" for any further filters.

This implies that there is no toggle selection, in which the selection of selected data (green) deselects it again. Meaning, once data is filtered it keeps this state and can from there on only be further narrowed down by adding more filters.

As a result, an "unlock" of selection state cannot happen, which ensures high security for sensitive data.

In most places you can easily change the order of filters by drag and drop:

"No data in the selected object filter"

If it happens that your filters come to a point where no "white data" (data that is still available and match all previous filters) is left, when after applying all your filters no records match the conditions, or when a filter is pointing to "grey data" (already excluded data) the generated report export will show objects with the message "No data in the selected object filter".

For example, if you first set a filter to fiscal month "Jun" for the whole report and then set another filter in customers to "City Fresh Foods" for a certain element, it will show the information on the export, that there was no data left to match those criteria (see image below) for this element. The other elements where only the first report filter applies will show as expected.

This message will also appear when your filter expression is not correct instead of just not applying the incorrect filter, which gives you an extra level of security.

Hierarchy

You can specify multiple filters on various levels and places. Understanding how filtering works makes clear that the order of filters is crucial. There are two kinds of areas where filters can be specified: From where the export of a report gets triggered, either automatically by a Job in "Jobs" or manually with the On Demand feature in "On Demand Reports" or in the Report Designer where the content creation happens. This results in the following hierarchy / the order in which filters are applied: Job filters or On Demand filters: Depending on where the export gets triggered (Job filters are also set in the Designer, but only apply if triggered by a Job) Report filters: Apply to the whole report Page Cycle: Generating a certain Designer page several times by looping values of a Qlik field, meaning this filter value applies to this designer page. Element filters: Only apply to specific elements. Report Calculation Condition: After all others, a condition can be set to determine if the report should get exported at all.

How to specify a filter

A filter basically consists of three levels of information:

1. The Qlik Source from where the data comes
2. The Qlik field in which you want to filter
3. One or more values / records from this field

One filter = one Qlik field = one or more values of this field.

1. Source

Depending on where you set the filter (Report, Element, On Demand, etc.) you might need to specify the Qlik Source you're referring to first. When designing your report, the table, chart, and text elements will have the Source specified in Content. Setting a Page Cycle or a report filter will only be enabled when selecting a Source there first. 

Exception:
Executing a report out of the On Demand feature can allow you to set filters there as well (depending on the specified permissions). If this report has more than one Source, the filter will be applied in all of them.
So keep this in mind when giving access to reports via On Demand and allowing to set additional filters on the fly.

See all settings details in the respective articles (related articles on the right).

2. Field

If Load Metadata has been executed previously in the source, you will have access to a suggestion list of all available field in your Qlik app (from the time the metadata was fetched). However, this is only for convenience. You can always set the field name manually as well.

Pay attention to exact notation/spelling of the field name and make sure to give the correct field name as it is in the data model of your Qlik app and not master item dimensions.

3. Value

Same as for the field name, it is very important to make sure the notation/spelling as well as the format (text, number, date, etc.) of a field value is exactly like it is in your Qlik app source.

You can specify one ore more values from a field, either by listing them or by expression.
However, the output/result must be one of the following: 

• Single value e.g., 2022
• Multiple values separated by semicolon e.g., 2020;2021;2022
  When given multiple values, Toggle Select and the OR logic applies, meaning at least one of those values must be true.

Create dynamic filters by using operators (for numeric values), wildcards (for text values), integrated ancoreShare variables or of course all kinds of Qlik expressions.

Toggle Select

When specifying multiple values, the toggle select logic applies, just like in Qlik.
Meaning that if a value is specified in the filter a second time, it gets deselected again.

This makes most sense when you want to exclude particular values from a given condition, for example:

>=1<=5;3

This filter specifies the numbers from 1 to 5 and number 3, but as 3 is already true in the first part it toggles the selection on this value.
Hence the result : 1;2;4;5 

Operators (numeric)

Integrated in ancoreShare are some operators you can use when specifying numeric values:

But make sure the field values in the data model of your Qlik app source are numeric as well, for example a field with years is often text and not numeric.

Wildcards (text)

Integrated in ancoreShare are some wildcards you can use when specifying text values.
Wildcards are placeholders for characters in text values.

Integrated ancoreShare variables

There are two kinds of integrated variables (not Qlik variables) that you can use to specify dynamic filters: General variables (day, month, etc.) Job filter variables (recipient information) You can easily paste them when adding filters in the Report Designer by selecting one in the drop down buttons. Besides date and time, the general variables also include the Page Cycle value.

Qlik Expressions

Besides the integrated operators and wildcards, you can use any Qlik expressions and variables from your Qlik app source to specify your filter.
ancoreShare enables you to access full Qlik expression functionality, like Set Analysis and variables, for specifying filters. 

Begin every expression with an equal sign =.

The process behind it:
When beginning with an equal sign, everything that comes afterwards will be handed over to Qlik, will be calculated and interpreted by Qlik, and the resulting values will be handed back to ancoreShare for further processing.

Hence, it's best practice to test or even create your expressions directly in Qlik, to check how the results will look and if the formula is correct. Best is to do this in a Qlik Text object.
This way you also see exactly how the result looks like and what to expect as an output in ancoreShare.

Combination

You can combine ancoreShare operators and wildcards with Qlik expressions.

To do so, put the operators or wildcards in 'single quotes'.
Connect expression parts and single quoted ancoreShare parts with ampersand &.

= expression part & ' operator ' & expression part

Operators, wildcards, and the integrated ancoreShare variables are interpreted by ancoreShare and Qlik expressions (including Qlik variables) by Qlik.

Examples

Highest and Lowest (numeric)

The highest and the lowest value of a field with numeric values:

=min(Year);=max(Year)

Sample result: 2000;2022 

Top two rank (text)

Ranks the top two values of a field according to a measure:

=concat({<Customer = {"=Rank(Sum(Sales)) <=2"}>} DISTINCT Customer,';')

Sample result: Alice;Bob

Last three months

This returns the past three months from the current point of time and filters those values:

= '>=' & num(monthstart(AddMonths(today(), -3))) & '<=' & num(monthend(AddMonths(today(), -1)))

Sample result when current month is 2022/06: 2022/03;2022/04;2022/05

Note: Make sure the output format matches your values in the Qlik field.
Use num() to specify the format.

All dates this year

This returns all dates since the beginning of the current year:

= '>=' & yearstart(today())

Note: Make sure the output date format matches your values in the Qlik field data.
Use num() to specify the format.

Qlik variable

You can also use any variables you created in your Qlik app source:

=vFilter

This will return all values of the Qlik variable vFilter from the specified source.