Uwe 

The rule

Inside a Ninox filter, whether [ … ] or where …, the condition is evaluated once for each record of the filtered table. If you put an if … then … else … end expression in that condition, Ninox 4 may not evaluate it correctly. And the serious part is that it throws no error: the filter simply finds no records, and the result comes out as 0 or empty.

That's why it's hard to detect: the formula saves without errors and looks correct, but the number is wrong.

Real example: Total Days

❌ Version that failed (returned 0 with 5 approved days):

sum(employees.absences[number(type) = lt and number(status) = 2 and

 year(if date_start != null then date_start else date_partial end) = yr].hrs_absence) / 8

The problem is in year(if … end) = yr, an if placed inside the filter brackets.

✅ Correct version (returns 5): move the if out of the filter and loop through the records:

let tot := 0;

for a in employees.absences do

 if number(a.type) = lt and number(a.status) = 2 then

  let d := if a.date_start != null then a.date_start else a.date_partial end;

  if year(d) = yr then

   tot := tot + a.hrs_absence

  end

 end

end;

tot / 8

In the loop, each condition is a normal statement, not part of a filter, so Ninox evaluates it without problems.