Filtering Data with Monarch

Finding the Needles in the Haystack

One of the main reasons that we use data extraction tools is to help us make decisions based on that data. And while the entire data set is certainly useful, often it’s the outliers, the values that are distant from the rest, or the values that exceed certain upper or lower known thresholds, that interest us the most.

Monarch helps us find those items with its filters feature. When in the Table window, we can ask Monarch to show us only those records that match what we’re looking for.

Here are some typical cases where filters excel at mining data:

1. Isolating regional data: “Show me all of the sales for the Western region only, not everything nationally.”
2. Highlighting good or poor results: “Show me all of the products on which we’re earning greater than 40% margins.”
3. Spotlighting individuals: “Show me Bob’s sales.”
4. Limiting the data to specific time periods: “Show me the second quarter AR collections from within this annual report.”

You create filters by creating Excel-like formulas that Monarch calls expressions. These expressions are often comprised of field names, logical operators (>, <, =, etc.), and specific values.

You can also include Monarch’s functions (either the built-in functions or your own custom functions) in your expression.

Making the Rules

Monarch requires that you name the filters you define. Take advantage of this by naming the filter to clearly reflect what it represents. Avoid names like “AR Filter”. Instead, use names like “Southern region sales” or “Great Performers” or “Credit Notes Issued”.

Once you have created at least two filters you have the option of creating what Monarch calls a compound filter. That is, you can combine the filters to see the data that those individual filtering rules create.

Monarch allows you to use either the OR Boolean operator or the AND Boolean operator. When you specify that any of the filters you’ve listed satisfy the filter, Monarch imposes the OR operator. When you specify that all of the filters you’ve listed satisfy the filter, Monarch applies the AND operator.

It’s important to note that you cannot combine the OR and AND operators when defining a compound filter.

For instance, a compound filter defined as “Southern region sales” AND “Great Performers” would show you the great performers in the Southern region. A filter named “Active region sales” defined as “Southern region sales” OR “Northern region sales” OR “Western region sales” would include everything but the Eastern sales, if the sales were all defined in that manner.

Breaking the Rules

Here’s where things get interesting. You can define a compound filter made up of a combination of a single, or a number of, compound filters and a single, or a number of, formula-based filters.

Now you can define “Great Active Region Sales” as a compound filter using the “Active region sales” (which uses the OR operator) compound filter and the “Great Performers” formula-based filter, tied together with an AND operator.

This is an example of how you can effectively combine the OR and AND operators in one filter.

Of course complex filters may take a longer time to process than simple filters during data extraction, depending on the volume of data in question.

Filters enable you to mine that golden information in a pile of raw data and let you really excel with Monarch.