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: 
anthony_kinsell
Creator
Creator

Excluding data using Set Analysis

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?

1 Solution

Accepted Solutions
Not applicable

Hi,

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

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

View solution in original post

11 Replies
Not applicable

Hi,

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

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

Anonymous
Not applicable

Hi Anthony,

You could use E() function.

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

Regards

MultiView

anthony_kinsell
Creator
Creator
Author

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)

Nicole-Smith

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)

anthony_kinsell
Creator
Creator
Author

Hi Nicole,

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

Anthony

Nicole-Smith

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)


or


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

anthony_kinsell
Creator
Creator
Author

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

Not applicable

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...

nizamsha
Specialist II
Specialist II

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

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

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

All the 3 Sets will work