Page History

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.

Filtering Attributes

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.

filter("L_CITY in ('Paris','Berlin','London')") {
    return M_VISITS;
}

• Regular Expressions

Some examples for regular expressions:
'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

Filtering Indicators

Following example counts number of records which have Indicator's value of visits greater than 3.

count = 0
filter('M_VISITS > 3') { 
    count++; 
} 

return count;

Date filtering

When filtering dates, the only allowed format is 'yyyy-MM-dd'. Following example returns number of students who had an exam on 12^th September 2014.

filter("L_DATE in ('2014-09-12')") {L_STUDENT_COUNT}

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.

filter("date_part('month', L_DATE) = 9 AND date_part('year', L_DATE) = 2014") {L_STUDENT_COUNT}

Time filtering

When filtering time, the only allowed format is 'HH:mm:ss'. Following example returns number of patients who arrived at 10:00

filter("L_TIME in ('10:00:00')") {L_PATIENT_COUNT}

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.

filter("date_part('hour', L_TIME) =10") {L_PATIENT_COUNT}

Multiple Filters

Following example returns number of students who had Exceptional results from Math subject.

filter("(L_PASS_RESULT in ('Exceptional')) AND (L_PASS_SUBJECT in ('Math'))"){
  L_STUDENT_COUNT
}

Code Alternatives

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.

def f = createFilter()
f = andFilter(f, 'L_ATTRIBUTE', 'OperationType', 'value1')
filter(buildFilter(f)) {
  ...
}

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:

For operations NULL and NOT NULL, the second value is empty.

def f = createFilter()
f = andFilter(f, 'L_ATTRIBUTE', 'NULL','')
filter(buildFilter(f)) {
  ...
}

For operations IN and NOT_IN, it is possible to define more than one value for comparison.

def f = createFilter()
f = andFilter(f, 'L_ATTRIBUTE', 'IN', 'value1', 'value2')
filter(buildFilter(f)) {
  ...
}

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.

String f = filterGeoJSONContains('L_GEOJSON', '134.256028,-24.162618')
filter(f) { 
 L_NAME_COUNT
}

Another option is to use this filter to retrieve the name of a county or state where the GEO point is located.

String f = filterGeoJSONContains('L_JSON', '134.256028,-24.162618')
filter(f) { 
  crossValue('GEOJSON', 'L_NAME_FIRSTVALUE')  
}

Next Steps

• Calculating Frequencies
• Calculating Percentual Share in Drill-Downs
• Calculating With Members On Defined Level
• Calculating Average Cumulated Values
• Calculating Percentiles and Quantils
• Getting Last Available Value