You can set up filters for view in Filter dialog.
Sometimes it is more convenient to add filter directly to indicator formula. You can do this when:
- you are cross referencing data from other data sets.
- you do not want your users to change filters.
The filter function supports most of the operators supported by PostgreSQL such as in/not in, ilike, like/not Like, similar to/not similar to etc. "ilike" is similar to "like" but case insensitive to the pattern matched.
- Filter using operator: in/not In
Following example filters data to include only Paris, Berlin and London in their City attribute. Result returns aggregation of visits in these three citites.
- Regular Expressions
Stands for any single character.
Stands for any sequence of zero or more characters.
Denotes repetition of the previous item zero or more times.
Denotes repetition of the previous item one or more times.
Denotes repetition of the previous item zero or one time.
Denotes alternation (either of two alternatives).
Following example counts number of records which have Indicator's value of visits greater than 3.
When filtering dates, the only allowed format is 'yyyy-MM-dd'. Following example returns number of students who had an exam on 12th September 2014.
For filtering whole month or year, put date-part function inside the filter. Following example return number of students who had an exam in September 2014.
When filtering time, the only allowed format is 'HH:mm:ss'. Following example returns number of patients who arrived at 10:00
For filtering whole hours, minutes or seconds, date_part function can be put inside the filter functiion. Following example returns number of patients who arrived between 10:00 and 10:59.
Following example returns number of students who had Exceptional results from Math subject.
It is possible to write filters in formulas to different ways. First one is mentioned in examples above. The second one uses more programmatic approach. In few steps, the filter is created and then built.
Function andFilter() is used to add another condition to the filter. It is possible to add more than condition, by using multiple andFilter() functions.
Following operations are available:
|EQ||evaluates if two values are equal|
|NEQ||evaluates if two values are not equal|
|GT||evaluates if first value is greater than second value|
|GTE||evaluates if first value is greater than second value or equal|
|LT||evaluates if first value is lower than second value|
|LTE||evaluates if first value is lower than second value or equal|
|IN||evaluates if first value equals one of the values from the list|
|evaluates if first value does not equal any of the values from the list|
|NOT_NULL||evaluates if first value is NULL|
|NULL||evaluates if first value is not NULL|
For operations NULL and NOT NULL, the second value is empty.
For operations IN and NOT_IN, it is possible to define more than one value for comparison.
Filtering GeoJSON Values
Filters can be also used to evaluate whether a certain GEO point is located inside an area defined by o GeoJSON. To be able to use this function, PostGIS has to be installed on the server. Administrators can open Database Summary page to see whether PostGIS is installed or not.
Another option is to use this filter to retrieve the name of a county or state where the GEO point is located.