unusual calc behavior

[David Jondreau]

I've got a table that has records of stays by individuals at a homeless shelter. Each record has value for an admit date and may or may not have a value for the departure date (if the person hasn't left yet).

I've also got two globals that feed a report. I'm trying to calculate the total number of nights during the two dates in the globals.

Here's the calc:

Let([

today = Get(CurrentDate);

startReport = Report Globals::Start Date;

endReport = Report Globals::End Date;

admit = Individual Admittance::Individual Admission date;

depart = Individual Admittance::Individual Departure Date;

depart = Case(IsEmpty(depart); today; depart);

end = Min(endReport;depart);

start = Max(startReport; admit);

result = end - start

];

result

)

This gives me expected results for records with a depart date. But with no depart date, I'm getting very odd results. I get the correct answer half the time...literally. As an unstored calc, and in the Data Viewer, the result toggles between the correct number of days (end - start) and a different number (admit - start).

It's pretty weird. The results toggle on some records when I click in and out of the record.

I copy and pasted the two tables into a new file and imported the data with similar results, it seems a less common (10% of the time rather than 90%), but still there.

Any thoughts?

[IdealData]

Why have you got 2 declarations for "depart" ?

[David Jondreau]

Because if the field is null, I want to use the current date. I could have wrapped it all in one declaration, but I find this way clearer to me.

[comment]

How about a demo showing the problem? I cannot see anything obviously wrong with the calc, but I don't see your relationships, and I'd suspect them first (why aren't admit/depart local fields to begin with?).

[David Jondreau]

They two date fields are local, they're just presented with relationships in order to debug.

After opening it this morning, the unstored calc as a field seemed to be working ok, but not the exact same calc in the debugger. Then I closed and restarted again and everything's fine.

I'm in the Twilight Zone.

If I can get the file to replicate the behavior consistently, I'll definitely post it.