3 Replies Latest reply: May 17, 2013 1:06 PM by stejim0 RSS

    How to only see the Zero Totals

      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

        • Re: How to only see the Zero Totals
          Stefan Wühl

          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.

          • Re: How to only see the Zero Totals

            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.