topic Re: Exclude Values in Set Analysis in QlikView

Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 16:13:12 GMT

I am trying to get a count of DEFECT_ID created within the last 6 months (based on the earliest date in New status) where PRIMARY_DECISION_BODY is NOT SMG Committee.

In my example of a specific ticket, PRIMARY_DECISION_BODY is null.

This works:

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

But this does not:

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'},PRIMARY_DECISION_BODY-={'SMG Committee'}>} distinct DEFECT_ID)

Any idea why?

TIA!

Re: Exclude Values in Set Analysis

sunny_talwar — Wed, 09 Aug 2017 16:16:57 GMT

When you say it doesn't work, is it giving you an error or incorrect output?

Re: Exclude Values in Set Analysis

swuehl — Wed, 09 Aug 2017 16:21:03 GMT

Maybe something like

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'},

DEFECT_ID = e( {< PRIMARY_DECISION_BODY ={'SMG Committee'}>})

distinct DEFECT_ID)

Excluding values in Set Analysis

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 16:22:45 GMT

Incorrect output. Shows 0 when I expect 1.

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 16:23:52 GMT

I still need to be able to filter on fields for this calculation. This looks like it's taking the entire population?

Re: Exclude Values in Set Analysis

sunny_talwar — Wed, 09 Aug 2017 16:26:06 GMT

May be this

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'},

DEFECT_ID = e( {<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'}, PRIMARY_DECISION_BODY ={'SMG Committee'}>})

distinct DEFECT_ID)

Re: Exclude Values in Set Analysis

swuehl — Wed, 09 Aug 2017 16:28:27 GMT

It should not return the full population, it should apply any filters your user selects, except the defined DEFECT_STATUS_START_DATE and DEFECT _ID. If you makes selections on DEFECT_ID, maybe add the intersection operator (and maybe use the 1 set identifier in any case in the e() function set expression:

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}, DEFECT_STATUS={'New'},

DEFECT_ID *= e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})

distinct DEFECT_ID)

Re: Exclude Values in Set Analysis

Anonymous — Wed, 09 Aug 2017 16:29:34 GMT

Based on Stefan's linked post. I would be interested if =- would work. Like so.

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 16:31:07 GMT

Thank you!

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 16:52:23 GMT

I tried that but it did not.

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 16:55:45 GMT

Dang - I seem to have lost the logic in my original post to take the min Start Date for where Status = New.

In other words, the New status could appear more than once for a DEFECT_ID. I only want to include those where the min Start Date of DEFECT_STATUS = New is within the last 6 months.

This is giving me a set modifier error. Where am I missing a ")"?

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

DEFECT_ID *= e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})

>}

distinct DEFECT_ID)

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 17:19:42 GMT

stalwar1‌

Re: Exclude Values in Set Analysis

sunny_talwar — Wed, 09 Aug 2017 17:28:59 GMT

The above seems to be a new expression, isn't it?

Re: Exclude Values in Set Analysis

sunny_talwar — Wed, 09 Aug 2017 17:36:33 GMT

May be you accidentally deleted part of the expression

Count({$<DEFECT_ID = {"=Min({1<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}>} DEFECT_STATUS_START_DATE) < Today()"}*e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>} DISTINCT DEFECT_ID)

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 17:49:52 GMT

thanks!

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 19:41:26 GMT

But now sometimes when I filter the numbers aren't changing... Filtering by region does change the number but selected a specific defect_ID does not. For example:

='Tickets Created: ' & Count({$<DEFECT_ID = {"=Min({1<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}>} DEFECT_STATUS_START_DATE) < Today()"}*e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>} DISTINCT DEFECT_ID)

If I select only one defect, I would expect to see 1 but I still see the total number for the selected region:

Re: Exclude Values in Set Analysis

sunny_talwar — Wed, 09 Aug 2017 19:45:27 GMT

Give this a shot

='Tickets Created: ' & Count({$<DEFECT_ID *= {"=Min({1<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({1<DEFECT_STATUS={'New'}>} DEFECT_STATUS_START_DATE) < Today()"}*e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>}DISTINCT DEFECT_ID)

Re: Exclude Values in Set Analysis

cbaqir — Wed, 09 Aug 2017 19:47:15 GMT

Looks like it worked! I don't know how you know all of this stuff! Thanks!