If you're using the standalone/single user version of Monarch, you should install [URL="http://www.datawatch.com/_support/downloads_updates.php"]the v11.1 patch[/URL] and then test your problem model again.
I could just be imagining things, but I think version 11 (even with the newest patch) "forgets" its calculated fields from time to time, populating them entirely with null values. It can be really frustrating.
Refreshing external data seems to cause Monarch to "remember" to calculate its other calculated fields. I've gone so far as to add an external lookup to models that have critical calculated fields just so that I can refresh the data when this happens. At the risk of sounding like a complete fool, try this (if you don't have an existing external lookup in that model):
Create an excel workbook with the number 1 in cell A1 and the number 2 in cell B1 (or whatever you want in B1), and save this workbook wherever you tend to keep external lookup sources for Monarch. (I can't remember off the top of my head if Monarch will want headers for those values. Maybe you'll need "One" in A1, "Two" in B1, 1 in A2, and 2 in B2. But I think you can do it without headers. Regardless, you're just matching the calculated field you created a step ago against your Excel workbook and returning anything at all.)
If creating this external lookup doesn't immediately cause Monarch to remember its other calculated fields, then try refreshing external data. If that doesn't do it, then I really must sound like a fool. In my defense however, it works for me when calculated fields return nothing but null values.
Of course, I find this a bit frustrating too, as I don't even have v11 yet to test along with you.
But the solution, or workaround, such as it is, as you've described is brilliant. Hey, whatever works, until such time as v11 works as intended! (Assuming of course that it doesn't quite work right now in this regard.)
I'm going to put this to use because I've become quite frustrated with version11 and its issue of needing to be prodded to refresh Calculated Fields, Sorts & Filters,
but 'til the 11.1 patch, I had too many other issues with the Monarch 11 Pro "upgrade".
While the developers had included buttons to refresh External Lookups, Sorts & Filters, they neglected to include a 'Refresh Calculated Fields' tool.
I hadn't figured out a consistent means of refreshing so. Thanks for thinking this one through.
For example, if I have the numeric fields:
The new calculated field below should return values:
I ecountered a similar problem in V11.1 with calculated fields that produced blank results.
V11 appears to handle Null values in calculated fields, in a slightly different way from earlier versions.
Where a calculated field tries to perform a calculation on a Null value, the result is Null.
The easiest way to see this, is to create a csv file in Excel with 3 records as follows:
Record FirstNumber SecondNumber
A 10 20
B 5 20
C (Leave Empty) 20
Open the csv file in Monarch and create a calculated field e.g. If(FirstNumber + 10 = SecondNumber, "TRUE","FALSE")
In Earlier versions, Record A would be True, Records B and C would be False.
In V11 Record A is True, Record B is False and Record C is Null.
A solution to this is to add a Null condition check at the start of the If statement e.g. If(IsNull(FirstNumber)=0 .AND.Firstnumber + 10 = SecondNumber, "TRUE,"FALSE)
This checks the field to make sure there's something in there, before performing the calculation.
Because of the use of .AND., the field contents have to pass both tests to be True.
For a Null entry, because the first test (the Null check) isn't passed, the calculation evaluates to False without needing to process the second half of the condition test.
For an entry that isn't Null, the first test is passed and the second test can be carried out because there is definitely something there to perform the calculation on.
In your example, I would try:
Bear in mind that IsNull needs to be the first condition test, whether using this with .AND. or .OR.
I'm awaiting a reply from Monarch support as to, whether this is an intended feature of V11.
Either way, it's probably a good discipline to include the Null check and it should still work, even if future versions of the Software revert to the earlier behaviour.
I'm not sure whether this is directly related to your problem but, hopefully it will be of use to you.
Edit: I've been informed that second level support have reported this as a bug so this behaviour might change in the next release.