Calculated field – compare a date field against current date
28 July, 2014
Hi,
I am trying to create a calculated field using the following logic but I cannot get it work.
CASE WHEN Remediation Date < current date THEN COUNT ( Finding ID ) END
Remediation Date contains dates. The calculated field will count the number of Finding ID if Remediation Date is before today.
My questions is how to compare Remediation Date against current date. I couldn't find such a function.
Thank you
Simon
I am trying to create a calculated field using the following logic but I cannot get it work.
CASE WHEN Remediation Date < current date THEN COUNT ( Finding ID ) END
Remediation Date contains dates. The calculated field will count the number of Finding ID if Remediation Date is before today.
My questions is how to compare Remediation Date against current date. I couldn't find such a function.
Thank you
Simon
Hi Simon,
In order to achieve what you're after, you will need to use a Freehand SQL Query.
This can be done through a virtual table , calculated field , or even using a freehand SQL report.
You will just need to ensure that your SQL is valid for the database you are using, and then make any modifications to it for it to work in YF.
E.g.
For SQL Server , I have a basic query ;
[code]
select date2 - GETDATE() from [Date / Time]
[/code]
In YF I had to change it to;
[code]
date2 - GETDATE()
[/code]
Please let me know how you go with this.
Regards,
David
In order to achieve what you're after, you will need to use a Freehand SQL Query.
This can be done through a virtual table , calculated field , or even using a freehand SQL report.
You will just need to ensure that your SQL is valid for the database you are using, and then make any modifications to it for it to work in YF.
E.g.
For SQL Server , I have a basic query ;
[code]
select date2 - GETDATE() from [Date / Time]
[/code]
In YF I had to change it to;
[code]
date2 - GETDATE()
[/code]
Please let me know how you go with this.
Regards,
David