Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

Hi,

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

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

View solution in original post

11 Replies
Highlighted
Not applicable

Hi,

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

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

View solution in original post

Highlighted
Creator II
Creator II

Hi Anthony,

You could use E() function.

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

Regards

MultiView

Highlighted
Creator
Creator

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)

Highlighted

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)

Highlighted
Creator
Creator

Hi Nicole,

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

Anthony

Highlighted

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)

Highlighted
Creator
Creator

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

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

Highlighted
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