Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi,
You can try Sum ({$<FundHeading -= {'Contract Sales'}>} DevBudgetAmount.
Or Sum ({$-<FundHeading -= {'Contract Sales'}>} DevBudgetAmount).
Hi,
You can try Sum ({$<FundHeading -= {'Contract Sales'}>} DevBudgetAmount.
Or Sum ({$-<FundHeading -= {'Contract Sales'}>} DevBudgetAmount).
Hi Anthony,
You could use E() function.
Example : Sum({$<FundHeading = (E({1<FundHeading={'Contract Sales'}>}))>} DevBudgetAmount)
Regards
MultiView
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)
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)
Hi Nicole,
It’s still showing items that have a fund heading of Rent Receivable & Rent Receivable Residential.
Anthony
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)
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
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...
=sum({$-<Title={"AA","AA2"}>}value)
=sum({$<Title-={"AA","AA2"}>}value)
=sum({$<Title={"*"},Title-={"AA","AA2"}>}value)
All the 3 Sets will work