Getting it Your Way

Do you find that you’re often creating elaborate, hard to read formulas in your expressions? Do you work with other Monarch users who aren’t quite as adept as you at writing expressions? You can replace that complexity with a simple to read, easy to use custom function. Introduced in version 9, Monarch now allows you define your own functions just like the other Monarch functions with which you’re already familiar.

When you begin defining a new custom function, you first give it a name. Make this as meaningful as possible, but brief. You cannot include any spaces in your function name, so you might want to use an underscore to make longer names a bit more readable. The name PayRate is pretty clear, but something like salarystaffpayrate is a little hard to read. Try Salary_Staff_Pay_Rate instead.

Let’s walk through an example. We’ll define FutureDate as a function that returns date type data. Set the description to read as follows:

• FutureDate(BaseDate,OffsetDays) returns the date of a given <1>start date</1> and the <2>number of days into the future</2>.

Note the numbered tags in angle brackets, like <tag>, just like you’d see in HTML, if you’re seen how web pages are created. The tags tell Monarch to bold this text when you’re supplying that parameter to the function when you’re using it in a calculated field. This happens in the shaded box below the expression editor in the calculated field dialog box.

What’s a Parameter?

It’s by supplying parameters, or named values, to your function that you can tell the function what to do, and therefore what you expect the function to give back to you.

The parameters for our function are BaseDate, a date field which will be the start date, and OffsetDays, a numeric field which represents the number of days after the start date. List both of these in the Parameter grid on the Parameters tab- each on its own row in the grid.

Now on the formula tab, set the expression to:

BaseDate+OffsetDays

Click the OK buttons to get back the list of custom functions, and stop right there. On the right side of the window, you’ll see the description you wrote for your function, just like the regular Monarch functions. Very cool.

Using Custom Functions in Calculated Fields

Let’s put our shiny new function to use. Create a new formula-based calculated field named ExpiryDate. Be sure to define it as a date field, because that what we defined the function’s return data type to be.

On the Formula tab, type FutureDate(. Stop it right there. See how Monarch shows you the required parameters, and bolds the name of the current parameter? Nice. Hard to get it wrong now, isn’t it. No wondering if you were meant to supply the number first, then the date, or the date then the number. Or, if you had no clue at all as to what the function needs, now you know.

Double click the {date} operator and pick a date. Now add a comma (see how the help changes in the box below?), and the value 90, because whatever it is that expires will do so in 90 days, for instance. Add the final right parenthesis to end the function.

Now click the Test tab. If you haven’t used this tab before, it’s a great addition to Monarch that lets immediately see how your calculated field is working.

Click the Test button at the bottom left, and if you’ve done everything properly, you’ll see a date 90 days later than the date you supplied for the first parameter in your function.

Of course, the date you supplied could have been a date you captured in your report.

Congratulations! You’ve added a whole new functionality to Monarch all on your own that didn’t exist previously.

Admittedly this is a pretty simple example, but honestly, a more complex function is just more of the same stuff. Monarch ships with a whole bunch of custom functions included. You can look at how they were built and learn more about custom functions - just like you did with your FutureDate function.

Test your functions!

Be careful about your data types, and test your functions to make sure that they work the way you intend them too. This is really important. Just as you want your calculations to be correct in Excel, you want to know that your Monarch model creates accurate results. Every time.

Monarch gives you unlimited power!

Well, almost unlimited. Monarch’s custom functions give the end user - you and I - the power to do what programmers sometimes call encapsulation. Essentially that means that it hides the complexity required by providing a simple means to do something. In this case, we can hide complex expressions and calculations by employing easy to use functions with clear, meaningful names.

Put to good use, custom functions really push the limits and truly let you excel with Monarch.