Exclude Values in Set Analysis

[cbaqir]

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!

[cbaqir]

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)

[cbaqir]

stalwar1‌

[sunny_talwar]

The above seems to be a new expression, isn't it?

[sunny_talwar]

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)

[cbaqir]

thanks!

[cbaqir]

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:

[sunny_talwar]

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)

[cbaqir]

Looks like it worked! I don't know how you know all of this stuff! Thanks!