Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to get a count of DEFECT_ID created within the last 6 months (based on the earliest date in New status) where PRIMARY_DECISION_BODY is NOT SMG Committee.
In my example of a specific ticket, PRIMARY_DECISION_BODY is null.
This works:
Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'}}>} distinct DEFECT_ID)
But this does not:
Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'},PRIMARY_DECISION_BODY-={'SMG Committee'}>} distinct DEFECT_ID)
Any idea why?
TIA!
Dang - I seem to have lost the logic in my original post to take the min Start Date for where Status = New.
In other words, the New status could appear more than once for a DEFECT_ID. I only want to include those where the min Start Date of DEFECT_STATUS = New is within the last 6 months.
This is giving me a set modifier error. Where am I missing a ")"?
Count({$<DEFECT_ID = {"=Min({1<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'},
DEFECT_ID *= e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})
>}
distinct DEFECT_ID)
The above seems to be a new expression, isn't it?
May be you accidentally deleted part of the expression
Count({$<DEFECT_ID = {"=Min({1<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}>} DEFECT_STATUS_START_DATE) < Today()"}*e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>} DISTINCT DEFECT_ID)
thanks!
But now sometimes when I filter the numbers aren't changing... Filtering by region does change the number but selected a specific defect_ID does not. For example:
='Tickets Created: ' & Count({$<DEFECT_ID = {"=Min({1<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}>} DEFECT_STATUS_START_DATE) < Today()"}*e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>} DISTINCT DEFECT_ID)
If I select only one defect, I would expect to see 1 but I still see the total number for the selected region:
Give this a shot
='Tickets Created: ' & Count({$<DEFECT_ID *= {"=Min({1<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}>} DEFECT_STATUS_START_DATE) < Today()"}*e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>}DISTINCT DEFECT_ID)
Looks like it worked! I don't know how you know all of this stuff! Thanks!