Well here we are again with another unexpected interruption of the 30 Days to Become a Better Monarch Modeler series. This time around, I had to take an unplanned business trip that threw a bit of a proverbial wrench in my machine. This was followed by returning to a sick family, and eventually and inevitably, becoming ill myself. Then a power failure during a stormy day managed to fry the external hard drive on which I stored the next couple of series posts which I’d prepared in advance despite the (now clearly inadequate) power protection system.

Regardless, we’ll still have 30 “episodes”; it’ll just take a little longer than planned to get through them. Sorry folks.

Any time that we work with raw data, more often than not we’re not really interested in all of the data that we have access to in this particular set, and it’s difficult to see the forest for the trees, as it were.

To that end, Monarch provides a fantastically rich ability to isolate important data through a mechanism that it calls Filters.

On day 12 we’ll introduce this vital feature to the 30 Days to Become a Better Monarch modeler series.

Filtering in Monarch is similar to using a search engine to find things of interest on the Internet. Usually it’s true that the better phrased the search, the better the results returned by the engine. Monarch acts the same way. Think of filters as Monarch’s way of searching for specific data within a larger set.

When do we want to employ Monarch’s filters? They are most beneficial when:

• The original data set is very large, and we’re only really interested in a particular subset for the task at hand. An example might be a report that’s been run for company-wide data, but only data for a department or two is currently required.
• When we need to focus very exactly and narrowly within the available data. Maybe we need to see the products shipped to southern states in the 15 days prior to the end of the quarter that generated greater less than 15% margins.
• When we cannot avoid bringing certain data into the set due to the methods used in developing the extraction templates. If it was not possible to avoid bringing the subtotal values that are interspersed throughout the report as the vendor changed into the data because there just wasn’t an apparent method to build the template without capturing those values, then they can be removed temporarily from the data by filtering out those subtotals.

Formula-based filters

In the end, there really only are three approaches to building filters:

1. To define the rules so as to include specific data,
2. To define the rules so as to exclude specific data,
3. To define the rules so as to include one aspect of the data and exclude another.

What’s the right and most efficient approach to take? It depends.

But no matter which approach is used, it will help you as a Monarch modeler to be entirely familiar with each of the available logical operators, and as your filter expressions grow in complexity, the order of operations and Boolean logic. Especially useful for filtering are the .In. and .NotIn. operators.

Monarch’s functions can play a huge role in building filter expressions, and allow for more flexibility than can possibly be demonstrated here, but it is worth mentioning that the RowNo() function cannot be used in filter expressions.

At times the filtering criteria can become quite complex; more than a few conditions are required to be met to generate the appropriate data. It’s then when Monarch’s ability to define multiple filters, and to combine specific individual filter, that make Monarch shine when compared to other software. Even Excel doesn’t come close to Monarch ease of use in this regard.

Monarch offers the ability to define a filter that is made up of other filters. Monarch calls these compound filters. In a prior paragraph there’s an example of filtering sales by geography by date by margin. To make the possible complexity more manageable, it’s often best to break a filter such as this down to the basic building blocks.

To do this, build three “formula-based” filters. First, create a filter than returns only the sales that occurred in the proper geography. Name this filter “Southern Sales”. Next, create another filter that returns all sales within a certain date range. Name this filter “Quarter End Sales”. Finally, create a filter that returns low margin sales (below 15%), and name this filter “Low Margin Sales”.

Compound Filters

To create the final filter, create a new filter but this time select the “Compound” option instead of “Formula-based”. In the Components tab of the dialog, assign a meaningful name to the filter, such as “South Low Margin End Qtr Sales”. You only have 31 characters for filter names, so name them wisely. Click on the items in list of available filters. You can Shift-click to select a range of filters, or Ctrl-click to select a collection of individual filters. Next, click the Add button to include the selected filters in the new filter. Notice that a compound filter expression is displayed in the bottom of the dialog. Our example would read [Southern Sales] AND [Quarter End Sales] AND [Low Margin Sales].

Notice that the option button is set to select rows that satisfy all of the component filters. Click the other option, “any of the component filters”. What does the filter expression read now? Each AND changed to OR. What would the effect be on the results returned by the filter?

What if we wanted to customize the filter a bit more, say to have the formula revised to read [Southern Sales] AND [Quarter End Sales] OR [Low Margin Sales]? Click the formula and revise the second AND to be OR. Well, that’s frustrating! Monarch won’t allow direct editing of the formula.

The solution is to save the first part, [Southern Sales] AND [Quarter End Sales], as a compound filter (South Quarter End), and then create a new compound so that the formula becomes [South Quarter End] OR [Low Margin Sales]. Using this technique we can define any filter imaginable.

Value-based filters

New to Monarch v10 is the ability to bring a list of values for a particular field into the filtering mechanism. The end result is the same as it would if a formula based filter had been created using only the .In. or the .NotIn. operator, but it’s much easier to use as the various values don’t need to be keyed into a formula. Instead, the values from the current data set are brought into the filter dialog just as when building internal lookups. The difference here is that a column of checkboxes are available. To include a particular value in the filter, just click its “Select” box.

As well, existing filters can be employed to limit the values that brought into the list. If it were up to me, I’d probably have placed the dropdown list of the filter names on the left side of the “Add current field values” button. As it is on the right side, it doesn’t tend to visually lead one to think that choosing a filter will affect the results of clicking the button, like it might if it were on the other side. Still, this is pretty minor and the functionality is terrific.

Your Task for Today

Monarch’s ability to isolate specifics from a vast sea of information is one of the first steps in the act of transforming data into information. It’s probably a fair statement to say that most of us just cannot mentally process huge amounts of information, and we find it much easier to decipher “bite-sized” chunks.

Now that you have a foundation in the filtering tools available, today you should create at least three filters using each of formula-based and compound mechanisms, and three more using value-based if you have Monarch v10. Mix it up and experiment with different functions for the formula-based filters. Before you finish build one more formula-based filter that duplicates the function of one of your compound filters, just so you can appreciate just how much easier it is to work with compound filters.

Don’t Strain Yourself

There’s no point in making your data and information tasks more difficult than they need be.
Not that long ago I had a coworker who, every Monday morning, would print a lengthy report and would meticulously review the stack of paper with a ruler and three highlighters in hand, essentially filtering his data. What a waste of time, effort and resources.

Stop working for your data. Make your data work for you. Filters are easy to use, easy to manage, and make it easy to excel with Monarch.

—

Continue your commitment to Become a Better Monarch Modeler with Part 13 of the series, or review Part 11.