Hi @ndeccio. This is a slightly more complex issue, as from what you’ve already gathered, the value must exist in order to reference/calculate from it. It sounds as though you’ve found a way to do this, but if it helps at all, this may be how I would have approached it.
The easiest way to do as you’re asking is probably to either include an additional calculated field on the data extract (ie SQL query) or add a transform column within the database design in order to generate the required value (assuming you have access to designer - if not you may need to arrange Phocas to undertake such a change for you)
For example within the design you could add a transform column that effectively ran a case statement over the date and evaluated against your criteria. This does assume you are evaluating a date type field, as otherwise you would need to convert before evaluating. You would end up with something along the lines of;
CASE WHEN [DateEntered] < dateadd(d, -20, Getdate()) THEN 1 ELSE 0 END
This would show all records with a date entered greater than 20 days ago as having a 1. Everything newer would be 0.
If however you wanted to evaluate every date so you could reference the age as a property, then you could do the following;
This could then be mapped to a property and formatted as a number so you could use in calculations,. You should then be able to use this within your search.
I hope this helps!