Using functions to create filters

You can use an Analytics function to create a filter. Different functions allow you to create different kinds of filters, depending on your particular need.

What's the purpose of a filter?

Filters are a basic and critical component of data analysis. Filters allow you to exclude records that you are not currently interested in, and include only the records that you want to examine.

If you have a table with a million records, and you want to examine only a small portion of them, you need a filter of some kind.

How a filter works

A filter is an expression that evaluates the records in a table and returns a value of "T" (True) or "F" (False) for each record.

Example

You want to examine only those amounts in an accounts receivable table that you consider material. Your threshold for materiality is $1000.00, so you create the following filter:

Invoice_Amount >= 1000.00

This filter returns True for amounts greater than or equal to $1000.00, and False for amounts less than $1000.00. Records that evaluate to True are included by the filter, and records that evaluate to False are excluded.

Excluded records are hidden from view while the filter is applied, and they are excluded from any Analytics commands you run on the table.

More sophisticated filters

You can use operators such as the Greater Than > and Less Than < signs to create simple filters, but using functions you can create more sophisticated filters.

Filter by date

We can use a version of the BETWEEN( ) example from the previous tutorials to create a filter that includes only invoices from the first quarter.

  1. In Analytics, open Sample Project.ACL, and open the Ap_Trans table (Tables\Accounts_Payable\Ap_Trans).

    If Sample Project.ACL is not available, open any table with a date field. To work with this example, the field must use the Datetime data type.

  2. Copy and paste this version of the BETWEEN( ) example into the Filter text box at the top of the View tab, and press Enter:

    BETWEEN(Invoice_Date, `20000101`, `20000331`)

    Result: The table is filtered to display only invoices from the first quarter of the year.

    If you not using the Ap_Trans table, update the field name and boundary dates in the BETWEEN( ) function to match your data.

    The field name must be the physical field name, not the display name (alternate column title). Right-click the header for the date field and select Properties to see both the physical and the display field names.

    Note

    Do not use DISPLAY in the Filter text box.

  3. Try changing one or both boundary dates to create a different date filter.

    When entering a literal date, you must use the format `YYYYMMDD`. If you are using the Ap_Trans table, all dates are in the year 2000.

Tip

You can also use BETWEEN( ) to filter numeric or text data. Enclose text inputs in "quotation marks". Do not enclose field names or numeric inputs in any punctuation: Invoice_Amount, 1000.00

Filter by multiple values

Now we'll use the MATCH( ) function to filter by multiple values simultaneously.

  1. Copy and paste the MATCH( ) function with these inputs into the Filter text box, and press Enter:

    MATCH(Vendor.Vendor_City, "Austin", "Chicago", "Salt Lake City")

    Result: The filter on the Ap_Trans table updates to display only invoices from vendors in the three specified cities.

    Note

    The Vendor_City field is in the Vendor table, which is related to the Ap_Trans table in Sample Project.ACL. To reference related fields in functions, you use table name.field name syntax.

    To reference fields in the open table, you use just field name.

  2. Try changing the field, and the three terms to match against, to create different sorts of filters.

    Note

    Search terms in the MATCH( ) function are case-sensitive.

Filter by fuzzy values

You should already be starting to see the power and usefulness of functions. This third filter uses the ISFUZZYDUP( ) function, which lets you filter by identical and nearly identical values.

Trying to perform a similar operation manually on a large table would be extremely time-consuming, if not impossible.

  1. Copy and paste the ISFUZZYDUP( ) function with these inputs into the Filter text box, and press Enter:

    ISFUZZYDUP(Vendor.Vendor_Name, "Miller Co", 4)

    Result: The filter on the Ap_Trans table updates to display only invoices from vendors with names that are identical or nearly identical to "Miller Co". You should see two records for the vendor "Muller Corp."

  2. Increase the degree of fuzziness from 4 to 8 and press Enter.

    An additional record for "MGMT Mfg." should now be included by the filter.

  3. Click Remove Filter , review the vendor names, and change "Miller Co" to something close to, but not exactly matching, one of the other vendor names.

    Experiment with different fuzziness settings. Valid settings are 1 to 10, inclusive.

The quick search in Analytics is really a filter

The quick search feature in Analytics is really a filter that uses the FIND( ) function.

  1. In the Ap_Trans table, click Remove Filter .
  2. Type the search term 931 in the Filter text box, and press Enter.

    Result: The table is searched for the characters 931 and two records are included in the filtered results:

    • one has an invoice number ending with 931
    • one has a product number ending with 931

Note that in the Filter text box your search term has been converted to the FIND( ) function with the input "931": FIND("931")

The right tool for the job

FIND( ) is another Analytics function that you can use for filtering data, or searching for specific items. It has the benefit of letting you search across all fields in a table.

But as you have already learned, there are other functions that give you additional powerful and flexible ways of filtering and searching data.

As you become more familiar with the entire set of Analytics functions, you'll discover that the function you choose depends on what you're currently trying to achieve.

Key point

You can use functions to create filters throughout Analytics, including in scripts. Filters created with functions are a fundamental building block of data analysis in Analytics.

Where to next?

Learn how to use functions to perform data cleansing or data preparation tasks: Using functions to clean data