PB DATE

[#1]

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.

[Gysbert_Wassenaar]

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.

[ali_hijazi]

make sure in the load statement to have both Close_date and date_notification as numbers

load num(daystart(Close_date)) as Close_Date...

[#4]

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.

[ali_hijazi]

us then the following syntax:

Count (if(Close_Date >=num(DayStart(AddMonths(date(date_notification),6)))),  DISTINCT FINDINGS_ID))

it should work

[#6]

Thank you very much.

It gives me that the expression is not correct.

[ali_hijazi]

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))

[#8]

it does not accept the DISTINCT in the expression !!!!!

[ali_hijazi · Accepted solution]

Count(DISTINCT

if(

Close_Date >=num(DayStart(AddMonths(date(date_notification),6))),  FINDINGS_ID))

[DavidFoster1]

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)