During my teen years in grade school I was a consummate math geek. I “trained” and competed in national math contests, mostly for fun. Who’d have thought, huh?
In retrospect, I probably should have gone into something like engineering, but computing drew me in. It was probably the possibility of encountering and solving endless calculations and challenges.
Nowadays Monarch feeds the compulsion. 
On day 8 of the 30 Days to Become a Better Monarch Modeler series, calculations take center stage.
The Magical Four
For many of us, Monarch would be a fantastic tool if the only thing that it did for us was purely report mining – extracting data from report files. But Monarch’s abilities have grown nicely over the years, and its current collection of calculation tools adds immense flexibility and power to the product.
For those of us who are relatively new to Monarch and formula building in general, I’d recommend reviewing The Basics of Building Monarch’s Expressions before getting too far along into developing calculated fields. The concepts covered in that post also relate to Excel, and any other software that lets you compare multiple values and conditions.
In a short series of posts from almost two years ago, I wrote about Monarch’s field types, and the four types of calculated fields. These include:
- formula-based calculated fields,
- runtime parameters,
- lookup fields, and
- user-edited fields.
I might be mistaken, but I think that those posts still hold up a good primers. Monarch’s functionality hasn’t changed significantly since they were written. So rather than rehash what’s covered there, it might be useful to supply some examples of each type of calculated field instead.
Formula-based
What’s the first thing that comes to mind when you think of formulas and math? I tend to think of basic math, like multiplication and division and such. I probably spent too much time in cost accounting. If you thought of something similar, then you’re on the right track: Monarch is perfectly capable of doing spreadsheet-like calculations to determine products, sums and the like. But I don’t think that basic math is why Monarch’s programmers gave it the ability to accept calculations.
Monarch’s main function is to mine data, and calculated fields help to refine what has already been captured. Sometimes, no matter how talented we become at defining just the right traps in our templates, it seems that some report designer just wanted to thwart us and mock us later. I can hear her now, “Ha! I told you that this was a closed system. You’re not getting my data even if you think that you can mine reports!” Well, I’ve got news for her…
By using Monarch’s special functions in formula-based calculated fields, we can carve up difficult data just like a Thanksgiving turkey (or a nice garden salad, if you prefer). This is the role in which formula-based fields shine. Just about any number cruncher with a spreadsheet can add value to a data set with a few mathematical calculations, but it’s tough to clean up messy data with a spreadsheet.
If you don’t believe me, try working with a 10 page report complete with headers and footers with Excel’s text to columns feature.
Once the data’s been cleaned up then we can add the other types of calculations and remove that work from the spreadsheet or database application.
Runtime Parameters
These fields let us interact with the Monarch model without editing it. They let us tell Monarch what value to use in calculations, like a commission rate or an exchange rate. They let us tell Monarch which customer we want to summarize. They let us tell Monarch to list the transactions which occurred between this date and that date.
Building runtime parameters into a model is easy, and integrating them into formula-based fields is child’s play. The true power of runtime parameters becomes apparent, in my opinion, when we integrate them into filter expressions, and optionally, use those filters with summaries.
Lookups
The logo that Datawatch uses for Monarch, and indeed the name itself couldn’t have been chosen better. Monarch transforms static, lifeless reports into dynamic, useful and actionable data. We might all be content with freeing our report data from its layout constraint.
When we introduce simple lookup fields the game changes: we begin to move from handling data to preparing information. With lookup fields we can add new meaningful and related data to empower us to provide better information. So much so that the entire extraction doesn’t just improve, but rather evolve.
The topic can be somewhat complex, and because of its importance to our work, I feel it best to delay a full discussion until we can devote an entire day to lookup fields.
User-edited
As the name implies, the purpose of these fields is to allow the editing of values by the model user. This is as close as Monarch gets to operating like a freestyle spreadsheet, only without the inter-cell connections and formulas.
When data (text, numbers, dates, times) is entered, it can be exported or printed, but when the model is closed, even if it was saved after the values were entered, the data is lost. It is not stored in the model for later use.
User-edited fields are, almost by definition, of limited use. They’re useful for tagging records while scanning a dataset for particular records, and then filtering based on the tag value.
And now, the editorial part of today’s post… Part of the attraction that Monarch has is that when people understand what it does, they trust the data that it delivers. I fear that some might distrust Monarch if they thought that they could be mislead by being presented with an extraction that included values from user-edited fields without their knowledge.
I have to admit that I struggle to find a truly effective and powerful use for user-edited fields. Maybe its simplicity is what makes it effective, and I’m just once again looking to overcomplicate things. If you have such a use for these fields, I’d love to hear about it.
Your Task for Today
Update: I missed the homework section when I originally posted this!
Today’s task is pretty straightforward: experiment with integration each type of calculated field into a single model. Be sure to set up the field and its calculation so that your output value matches the field type that you specified, or you’ll be greeted by a friendly error message.
Don’t worry too much about lookup fields if you prefer. As mentioned, we’ll cross that bridge soon enough. But if you really want to, have at it and have fun.
Monarch Computes!
If a value can be calculated, there’s a near certainty that Monarch can handle that work. In fact the justification to include the calculation in a Monarch model should increase with the complexity of the calculation, as it ensures that the data is accurate, and it shelters others from preparing that type of work.
Get accustomed to handling the different types of calculated fields available and as clear as a good proof, it’ll be obvious that you excel with Monarch.
—
Continue your commitment to Become a Better Monarch Modeler with Part 9 of the series, or review Part 7.
{ 1 comment… read it below or add one }
Re. User Edited Fields
Just to point out that the entries are not stored in the model but are stored in the project file – which is logical since the model is a model ‘for all time’ whereas the project is, potentially, ephemeral in that the report or database used (or at least the data in them) is more likely to change than not.
On the other hand in that case one would want the User Edited fields to be reset as well and I am not sure how that is dealt with.
If one needed this sort of feature regularly I would assume that the process would involve a default project with empty User Edited field or fields for which the incoming file would be changed as required and the project saved with a new name reflecting the version of the file. In that case the notes in the User Edited fields would continue to have validity and tracability and auditability would be better served.
It would be good discover the reasons for the request that led to the field being developed in order to obtain a more complete understanding of the potential just in case we have missed something even more useful.
Grant