21 Replies Latest reply: Jul 28, 2017 7:20 AM by Sunny Talwar

# Set Analysis Combination

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:

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

TIA!

• ###### Re: Set Analysis Combination

May be this for Req 1

What is the expected output for Req 2?

• ###### Re: Set Analysis Combination

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

• ###### Re: Set Analysis Combination

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

Did the first req looked good?

• ###### Re: Set Analysis Combination

Yes I think so

• ###### Re: Set Analysis Combination

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

• ###### Re: Set Analysis Combination

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?

• ###### Re: Set Analysis Combination

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

• ###### Re: Set Analysis Combination

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)

• ###### Re: Set Analysis Combination

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

The P() is the intersection, right?

• ###### Re: Set Analysis Combination

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.

• ###### Re: Set Analysis Combination

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

Thanks again, sir!

• ###### Re: Set Analysis Combination

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'}>}),

• ###### Re: Set Analysis Combination

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

• ###### Re: Set Analysis Combination

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 =
DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

• ###### Re: Set Analysis Combination

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.

• ###### Re: Set Analysis Combination

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

• ###### Re: Set Analysis Combination

For Req 2, I am getting 423

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

• ###### Re: Set Analysis Combination

thank you!

• ###### Re: Set Analysis Combination

No problem at all

• ###### Re: Set Analysis Combination

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)

• ###### Re: Set Analysis Combination

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)