Set Analysis Combination

[cbaqir]

2 requirements:

Requirement 1: combine base expression with: Of those created within the last 6 months, how many touched PROCESS_STEP={'Submitted to Routing'} ?

Base expression to identify tickets created within the last 6 months:

=Count({$<PROCESS_STEP_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},PROCESS_STEP={'Ticket Created (Detected)'}>} distinct DEFECT_ID)

Requirement 2: How many DEFECT_ID have a DEFECT_STATUS = Closed within the last 6 months but were created BEFORE 6 months ago?

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

TIA!

[cbaqir]

Ok, one more wrench. I need to be able to filter these text boxes by REQUEST_REGION and I think using 1 is preventing me from doing that. Is there any reason why it is needed because if I take it out, the numbers still look correct to me.

='ERC - Routing: ' & Count(

{1<GOV_PROCESS_QUEUE = {'ERC - Routing'}, DEFECT_ID = {"=Min({1<DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}>}

DISTINCT DEFECT_ID)

[sunny_talwar]

Yes that is true, I don't know your data model well enough to understand which selections were causing your expression to not work... but if you can find those replace 1 with the specific fields like this

='ERC - Routing: ' & Count(

{<Field1, Field2, Field3,.....,GOV_PROCESS_QUEUE = {'ERC - Routing'}, DEFECT_ID = {"=Min({<Field1, Field2, Field3,.....,DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<Field1, Field2, Field3,.....,DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}>}

DISTINCT DEFECT_ID)

Alternatively, if you only want this one selection, you can do this

='ERC - Routing: ' & Count(

{1<REQUEST_REGION = p(REQUEST_REGION), GOV_PROCESS_QUEUE = {'ERC - Routing'}, DEFECT_ID = {"=Min({1<REQUEST_REGION = p(REQUEST_REGION), DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<REQUEST_REGION = p(REQUEST_REGION), DEFECT_STATUS={'New'}, GOV_PROCESS_QUEUE = {'ERC - Routing'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}>}

DISTINCT DEFECT_ID)