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]

May be this for Req 1

='ERC - Routing: ' & Count({$<DEFECT_ID = p({<PROCESS_STEP_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},PROCESS_STEP={'Submitted to Routing'}>}),

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)

What is the expected output for Req 2?

[cbaqir]

I don't know what the expected output is... I will try to write a query against the database to figure it out.

[sunny_talwar]

Yes, that would help me figure out what you might be after.

Did the first req looked good?

[cbaqir]

Yes I think so

[cbaqir]

I think the expected output is around 450 for req 2.

[sunny_talwar]

Got 426 using

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

What was your date range when you used in the query?

DEFECT_STATUS_START_DATE <= 01/31/2017?

[cbaqir]

-6 months so today it would be 1/26/17.

[sunny_talwar]

This gives 422

='Closed: ' & Count({$<DEFECT_ID = P({<DEFECT_STATUS_START_DATE = {"<=$(=Date(Addmonths(Today(),-6),'MM/DD/YYYY'))"}, DEFECT_STATUS -= {'Closed'}>})*P({<DEFECT_STATUS_START_DATE = {">=$(=Date(Monthend(Today(),-6),'MM/DD/YYYY'))"}, DEFECT_STATUS = {'Closed'}>})>} distinct DEFECT_ID)

[cbaqir]

This is Closed in the last 6 months but created more than 6 months ago?

The P() is the intersection, right?