ACE Analytics Filtering Rows

Click the Formula tab to use the feature that allows you to add calculated columns to the data.

Here's how to use this feature:

1. Select the Filter Column containing the values to be compared:

You'll see that the options are grouped and color-coded to make it easier for you to identify them. If you created any Formula columns, they'll be in there, too.

2. Set the filtering criteria by selecting a Comparison operator from the list.

Comparison operators include = , <, >, < =, > =, Not =, Starts With, Contains, Not Contains, and Does Not Start With. If the Filter Column is a date, then Date Range is available and some other options are not. The Starts With and Contains operators are useful for finding values at the beginning or within data and will work with both text and numeric data. The Not Contains and Does Not Start With operators work in the opposite manner.

Depending on the comparison chosen, additional input controls may be displayed, for example, for date ranges.

3. Enter a comparison Value. Wildcard characters (*, %) are not allowed in these values. Click Add. Rows that don't meet these criteria will be removed from the table.
4. As filters are created, they're added to the filter list. Use the adjacent Replace and Remove buttons to manage the list.

If you add multiple filters, initially rows that meet all the conditions will be retained (an "And" situation). Clicking the And button in the Filters list, shown above, changes it to an Or button, so rows that meet any of the conditions will be retained. A set of three icons will also appear by the Remove button. These can be used to re-order the precedence of the filters or to group them together in various arrangements using parentheses.

Filtering by Dates

If the Filter Column selected is a date type column, the interface presents different value controls:

You may choose to filter on a Specific Date and Time and either type it in or select it from a pop-up calendar. Or, as shown above, right, you can filter using a Sliding Date value and select from a long list of relative dates (Last Week End, Last Month Start, 90 Days Ago, etc.)

If the Comparison option Date Range is chosen, as shown above, different value controls for Starting and Ending dates, which can be used in a variety of combinations are displayed.

You can also use a combination of specific and sliding date comparisons.

When done, click the Filter tab to hide the panel.