Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to calculate an indicator whose objective is to calculate the Number of Findings still open 6 months after date of notification. So basically Each Findings, I have a closing date (CLOSE_DATE) and a notification date and I want to calculate the number of Findings that have a closing date than 6 months of their date of notification.
That's what I tried to do but it does not work :
Count ({$<CLOSE_DATE={">=$(=date(AddMonths(date_notification,6))"}>} DISTINCT FINDINGS_ID)
Thank you in advance.
Count(DISTINCT
if(
Close_Date >=num(DayStart(AddMonths(date(date_notification),6))), FINDINGS_ID))
Make sure that the fields CLOSE_DATE and date_notification actually contains data values and not string values. Also each finding should have only one closing date and only one notification date. Otherwise the comparison of the fields won't work.
make sure in the load statement to have both Close_date and date_notification as numbers
load num(daystart(Close_date)) as Close_Date...
Hello,
I loaded the two dates in Num format but it does not work yet.
num(daystart(date_notification)) as date_notification,
num (daystart ( CLOSE_DATE)) as CLOSE_DATE,
Thank you in advance.
us then the following syntax:
Count (if(Close_Date >=num(DayStart(AddMonths(date(date_notification),6)))), DISTINCT FINDINGS_ID))
it should work
Thank you very much.
It gives me that the expression is not correct.
I added an additional parenthesis by mistake; the following should work
Count(
if(
Close_Date >=num(DayStart(AddMonths(date(date_notification),6))), DISTINCT FINDINGS_ID))
it does not accept the DISTINCT in the expression !!!!!
Count(DISTINCT
if(
Close_Date >=num(DayStart(AddMonths(date(date_notification),6))), FINDINGS_ID))
I would suggest a simple solution would be to a flag field into your findings data that contains a 1 for findings within 6 months and a 0 for the rest.
That way your expression would then be SUM(Within6MonthsFlag)