Icon

    It is recommended to get familiar with filter function before proceeding with this tutorial.

    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.

    Icon

    Note the correct usage of apostrophs in the example.

    • Regular Expressions

    Pattern

    Description

    _

    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).

    Filtering Indicators

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

    Icon

    Note that filter is applied on every record and not on its aggregated value displayed in the view.

    Date filtering

    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.

    Time filtering

    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.

    Multiple Filters

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

    Icon

    You can combine multiple filter conditions with AND or OR conjunction.

    Code Alternatives

    Icon

    This feature is available since BellaDati 2.9.2

    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:

    OperationDescription
    EQevaluates if two values are equal
    NEQevaluates if two values are not equal
    GTevaluates if first value is greater than second value
    GTEevaluates if first value is greater than second value or equal
    LTevaluates if first value is lower than second value
    LTEevaluates if first value is lower than second value or equal
    INevaluates if first value equals one of the values from the list

    NOT_IN

    evaluates if first value does not equal any of the values from the list
    NOT_NULLevaluates if first value is NULL
    NULLevaluates 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

    Icon

    This feature is available since BellaDati 2.9.2

    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.

    Next Steps