Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Looking for some guidance on the following
Name Points
Person 1 0
Person 2 10
Person 3 0
Person 4 5
I'm trying to achieve a list of 'Names' where the points is zero.
Name Points
Person 1 0
Person 3 0
I am using the following expression
Sum (if ([Grand Total] = 0,[Grand Total]))
In the Presentation Tab, if I have "Suppress Zero-Values" selected Nothing shows on my chart which makes sense as all my values are zero.
If I uncheck "Supress Zero-Values" then I get
Person 1 0 ** Correct
Person 2 0 ** Incorrect
Person 3 0 ** Correct
Person 4 0 ** Incorrect
Am thinking this is a rookie mistake but don't know how to fix it.
Any assistance appreciated.
Regards,
Jim
Sum (if ([Grand Total] = 0,[Grand Total]))
If Grand Total not equals zero, the else branch of your if statement should be executed. There is no else branch, so NULL is returned. A sum of NULL is zero.
To resolve your issue, try this as expression and uncheck 'supress zero values' in presentation tab:
=Sum ( {<Name = {"=sum([Grand Total])=0"} >} [Grand Total])
'Name' being your Person field name.
Sum (if ([Grand Total] = 0,[Grand Total]))
If Grand Total not equals zero, the else branch of your if statement should be executed. There is no else branch, so NULL is returned. A sum of NULL is zero.
To resolve your issue, try this as expression and uncheck 'supress zero values' in presentation tab:
=Sum ( {<Name = {"=sum([Grand Total])=0"} >} [Grand Total])
'Name' being your Person field name.
Many thanks.
Being new to sets can you please explain how to read the solution?
Not the 'correct' answer I marked below. I cheated and solved it this way.
Sum (if ([Grand Total] = 0,-1,0))
Basically I set the zero values (which I wanted to see) to -1 and the non zero values (which I wanted to hide) to 0.
Then I marked the Supress Zero-Values checkbox to checked on the presentation tab
Then I hid the expression column (still on the presentations tab)
I just get then a list of names rather than a list of names and a column with zeros, which might be more correct than what I was originally trying to achieve.