Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
cbaqirdhds
Contributor 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!

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Exclude Values in Set Analysis

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)

17 Replies

Re: Exclude Values in Set Analysis

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

MVP
MVP

Re: Exclude Values in Set Analysis

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

cbaqirdhds
Contributor II

Re: Exclude Values in Set Analysis

Incorrect output. Shows 0 when I expect 1.

cbaqirdhds
Contributor II

Re: Exclude Values in Set Analysis

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

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)

MVP
MVP

Re: Exclude Values in Set Analysis

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)

atkinsow
Valued Contributor II

Re: Exclude Values in Set Analysis

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)

cbaqirdhds
Contributor II

Re: Exclude Values in Set Analysis

Thank you!

cbaqirdhds
Contributor II

Re: Exclude Values in Set Analysis

I tried that but it did not.

Community Browser