Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Exclude Values in Set Analysis

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

17 Replies
sunny_talwar

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

swuehl
MVP
MVP

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

cbaqir
Specialist II
Specialist II
Author

Incorrect output. Shows 0 when I expect 1.

cbaqir
Specialist II
Specialist II
Author

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

sunny_talwar

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)

swuehl
MVP
MVP

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)

Anonymous
Not applicable

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

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)

cbaqir
Specialist II
Specialist II
Author

Thank you!

cbaqir
Specialist II
Specialist II
Author

I tried that but it did not.