topic Excluding data using Set Analysis in QlikView

Excluding data using Set Analysis

anthony_kinsell — Mon, 13 Jan 2014 17:07:22 GMT

Hi,

I'm trying to do a graph of Budget vs Actual but don't want 'Contract Sales' included in my Budget figures.

I know the below formula gives me just Contract Sales but how do I exclude them:

Sum ({<FundHeading = {'Contract Sales'}>} DevBudgetAmount)

Tried various attempts of 1 - contract sales but can't seem to get it to work?

Re: Excluding data using Set Analysis

Mon, 13 Jan 2014 17:11:58 GMT

Hi,

You can try Sum ({$<FundHeading -= {'Contract Sales'}>} DevBudgetAmount.

Or Sum ({$-<FundHeading -= {'Contract Sales'}>} DevBudgetAmount).

Re: Excluding data using Set Analysis

Anonymous — Mon, 13 Jan 2014 17:20:20 GMT

Hi Anthony,

You could use E() function.

Example : Sum({$<FundHeading = (E({1<FundHeading={'Contract Sales'}>}))>} DevBudgetAmount)

Regards

MultiView

Re: Excluding data using Set Analysis

anthony_kinsell — Tue, 14 Jan 2014 16:20:30 GMT

If I wanted to exclude 2 headings?

I've tried something like this but it doesn't work:

Sum ({<FundHeading -= {'Rent Receivable Residential' , 'Rent Receivable'}>} ExpActualNett)

Re: Excluding data using Set Analysis

Nicole-Smith — Tue, 14 Jan 2014 17:07:44 GMT

That should work right like you have it. What is it doing that you say it isn't working?

Have you also tried:

Sum ({$-<FundHeading = {'Rent Receivable Residential' , 'Rent Receivable'}>} DevBudgetAmount)

Re: Excluding data using Set Analysis

anthony_kinsell — Tue, 14 Jan 2014 17:13:37 GMT

Hi Nicole,

It’s still showing items that have a fund heading of Rent Receivable & Rent Receivable Residential.

Anthony

Re: Excluding data using Set Analysis

Nicole-Smith — Tue, 14 Jan 2014 17:41:48 GMT

Do the values in your data have leading or trailing spaces? If the following works, you know this is true:

Sum ({$-<FundHeading = {'*Rent Receivable Residential*' , '*Rent Receivable*'}>} DevBudgetAmount)

Sum ({$<FundHeading -= {'*Rent Receivable Residential*' , '*Rent Receivable*'}>} DevBudgetAmount)

Re: Excluding data using Set Analysis

anthony_kinsell — Wed, 15 Jan 2014 09:40:39 GMT

No they don’t seem to:

Below is a screen shot of my table – as you can see I’m still getting headings with Rent Receivable:

I have also ticked the box for ‘suppress when value is null’ but this isn’t working either!

Anthony

Re: Excluding data using Set Analysis

Wed, 15 Jan 2014 10:56:46 GMT

It's still showing 'Rent Receivable' in your pivot chart, but results are 0 as they should be. To avoid showing 'Rent Receivable' in the chart, a possibility is to replace FundHeading dimension by if(index(FundHeading,'Rent Receivable')=0,FundHeading), select to not show nulls on this dimension, and name it FundHeading or another way...

Re: Excluding data using Set Analysis

nizamsha — Wed, 15 Jan 2014 11:38:33 GMT

=sum({$-<Title={"AA","AA2"}>}value)

=sum({$<Title-={"AA","AA2"}>}value)

=sum({$<Title={"*"},Title-={"AA","AA2"}>}value)

All the 3 Sets will work

Re: Excluding data using Set Analysis

Nicole-Smith — Wed, 15 Jan 2014 15:07:50 GMT

You need to either do what Pierre de Calan mentioned or check "Suppress Zero Values" on the Presentation tab.

Re: Excluding data using Set Analysis

anthony_kinsell — Wed, 15 Jan 2014 15:22:40 GMT

I have checked ‘Suppress Zero Values’ already – it doesn’t work