Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

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:

=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!

1 Solution

Accepted Solutions
sunny_talwar

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)

Capture.PNG

View solution in original post

21 Replies
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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

Yes I think so

cbaqir
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

-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
Specialist II
Specialist II
Author

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

The P() is the intersection, right?