This thread was helpful but I had the same problem as @rlatorre "invalid field or a field that is not visible (DATE_SUB)" when I tried to put a dynamic date into a view where clause. I did not want to filter my view later with JavaScript as the point of my view was for performance with a large dataset.
I found a solution based on this link:
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0719186
which suggests creating a "before query" business rule, and add the where clauses there using current.addQuery(). The only clause I had which didn't work in the view was the date clause, the rest were ok. So the only part I moved to the business rule was the date comparison.
Here is the content from the link:
I created a business rule, chose my view as the table, checked "advanced" and "query", when=before, and entered this code in the script using GlideDate().
To use @Manish Vinayak1 example for the last 6 months:
(function executeRule(current, previous /*null when async*/) {
// Add your code here
var gDate = new GlideDate();
gDate.addMonthsLocalTime(-6);
var query = 'inc_sys_created_on >= ' + gDate.getDate();
current.addQuery(query);
})(current, previous);
I found a few threads in the community but no solution so feel free to share this one.
