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!

[sunny_talwar]

P() is possible, intersection is done using *. But yes the idea is to find defects which were open 6 months ago, but are closed in the last 6 months.

[cbaqir]

Thanks. I just need to find out what the actual number is from the query. I will respond then.

Thanks again, sir!

[cbaqir]

Sunny,

I'm trying to modify your expression to find a count of those tickets where CUR_GOV_PROCESS_QUEUE is 'ERC - Resourcing' or 'ERC - Assigned', or ERC - Agenda' and the 'Ticket Created (Detected)' within the last 6 months.

What's wrong with this?

Count({$<DEFECT_ID = p({<CUR_GOV_PROCESS_QUEUE={'ERC - Resourcing','ERC - Assigned','ERC - Agenda'}>}),
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)

[sunny_talwar]

I am not sure, is it giving you error? or incorrect count?

[cbaqir]

It turns out getting query uncovered that I was using a field with logic to only look at defects with status = Closed.

You helped me get the logic of defects created in the last 6 months using only the min start date for New status and this looks correct:

='Tickets Created: ' & Count({$<DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}, DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

Requirement 1: Of those created within the last 6 months, how many have passed through GOV_PROCESS_QUEUE = ERC - Routing? Expected 282:

='ERC - Routing: ' & Count({$<DEFECT_ID = p({<GOV_PROCESS_QUEUE={'ERC - Routing'}>}),
DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}, DEFECT_STATUS={'New'}>} distinct DEFECT_ID)

Requirement 2: Of those created BEFORE the last 6 months, how many were closed within the last 6 months? I expect 454 here.

='Closed w/i 6 months; Created >6 months: ' & Count({$<DEFECT_ID =
p({<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'Closed'}>}),
DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"},
DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

[sunny_talwar]

For Req 1, I am getting 276

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

Attached chart will show you the list of DEFECT_IDs which are included in the 276 here.

[cbaqir]

Thanks - I think this is correct for Req 1 - my data is a little newer.

[sunny_talwar · Accepted solution]

For Req 2, I am getting 423

='Closed w/i 6 months; Created >6 months (expected 454): ' & Count({$<DEFECT_ID =

{"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) < AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'Closed'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6)"}>} DISTINCT DEFECT_ID)

[cbaqir]

thank you!

[sunny_talwar]

No problem at all