topic Re: Set Analysis Combination in QlikView

Set Analysis Combination

cbaqir — Tue, 25 Jul 2017 17:11:54 GMT

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!

Re: Set Analysis Combination

sunny_talwar — Tue, 25 Jul 2017 17:33:38 GMT

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?

Re: Set Analysis Combination

cbaqir — Tue, 25 Jul 2017 18:09:20 GMT

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

sunny_talwar — Tue, 25 Jul 2017 18:10:51 GMT

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

Did the first req looked good?

Re: Set Analysis Combination

cbaqir — Wed, 26 Jul 2017 14:25:36 GMT

Yes I think so

Re: Set Analysis Combination

cbaqir — Wed, 26 Jul 2017 14:32:28 GMT

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

Re: Set Analysis Combination

sunny_talwar — Wed, 26 Jul 2017 14:49:25 GMT

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

cbaqir — Wed, 26 Jul 2017 14:58:21 GMT

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

Re: Set Analysis Combination

sunny_talwar — Wed, 26 Jul 2017 15:00:19 GMT

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

cbaqir — Wed, 26 Jul 2017 15:00:30 GMT

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

sunny_talwar — Wed, 26 Jul 2017 15:01:57 GMT

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

cbaqir — Wed, 26 Jul 2017 15:04:00 GMT

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

cbaqir — Wed, 26 Jul 2017 19:28:30 GMT

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)

Re: Set Analysis Combination

sunny_talwar — Thu, 27 Jul 2017 11:15:42 GMT

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

Re: Set Analysis Combination

cbaqir — Thu, 27 Jul 2017 17:41:55 GMT

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)

Re: Set Analysis Combination

sunny_talwar — Thu, 27 Jul 2017 18:57:55 GMT

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

cbaqir — Thu, 27 Jul 2017 19:00:03 GMT

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

Re: Set Analysis Combination

sunny_talwar — Thu, 27 Jul 2017 19:03:28 GMT

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)

Re: Set Analysis Combination

cbaqir — Thu, 27 Jul 2017 19:04:24 GMT

thank you!

Re: Set Analysis Combination

sunny_talwar — Thu, 27 Jul 2017 19:07:29 GMT

No problem at all