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!

[sunny_talwar]

When you say it doesn't work, is it giving you an error or incorrect output?

[swuehl]

Maybe something like

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'},

DEFECT_ID = e( {< PRIMARY_DECISION_BODY ={'SMG Committee'}>})

>}

distinct DEFECT_ID)

Excluding values in Set Analysis

[cbaqir]

Incorrect output. Shows 0 when I expect 1.

[cbaqir]

I still need to be able to filter on fields for this calculation. This looks like it's taking the entire population?

[sunny_talwar]

May be this

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'},

DEFECT_ID = e( {<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)

[swuehl · Accepted solution]

It should not return the full population, it should apply any filters your user selects, except the defined DEFECT_STATUS_START_DATE and DEFECT _ID. If you makes selections on DEFECT_ID, maybe add the intersection operator (and maybe use the 1 set identifier in any case in the e() function set expression:

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'},

DEFECT_ID *= e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})

>}

distinct DEFECT_ID)

[Anonymous]

Based on Stefan's linked post.  I would be interested if =- would work.  Like so.

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)

[cbaqir]

Thank you!

[cbaqir]

I tried that but it did not.