6 Replies Latest reply on Oct 3, 2017 12:29 AM by Jayshree Dawrewar

    Count (Expression = True)

    goh shan ying

      Hi, everyone, i would like to add filter into my calculation field.

      This is my code:

       

      COUNT([Lunch] = "FALSE" ) / COUNT([Package Distinct]="0")

       

      However, it says cant compare boolean and string values, I need help...

      Can anyone help me form the calculation?

       

      I need Lunch to be false

      and package distinct to be 0

       

      the total number of lunch =false needs to be divided by number of package distinct = 0

        • 1. Re: Count (Expression = True)
          Tushar  More

          Is the lauch field boolean?

          Is the Package distinct field numeric?

           

          If yes, remove double quotes from the formula.

          COUNT([Lunch] = FALSE) / COUNT([Package Distinct]=0)

           

          Hope this helps.

           

          ~Tushar

          1 of 1 people found this helpful
          • 2. Re: Count (Expression = True)
            Jayshree Dawrewar

            Hi Ying,

             

            You have double quoted FALSE. If its a boolean field you don't have to keep FALSE in quotes (" ").

            Also same with the Integer 0.

            I agree what Tushar has suggested.

             

            Regards,

            Jayshree Dawrewar

            5 of 5 people found this helpful
            • 3. Re: Count (Expression = True)
              chris monger

              Hi

               

              As Jashree suggested ithe first section should just be

               

              COUNT([Lunch] = FALSE )

               

              Is the character to match on the second part a number 0 or a letter o? If it is the letter then it does need to be in quotes, but not for the number.

               

              Another way to do it, which might be easier to read would be:

               

              sum(if [Lunch] then 1 end) / sum(if [Package Distinct] = 0 then 1 end)

               

              edit: as Jonathan Drummey mentioned below the second part should be sum not count.

              • 4. Re: Count (Expression = True)
                Jonathan Drummey

                A warning on the prior suggestions, they will fail to exclude true values from the count and create incorrect results: COUNT([field]='False') OR COUNT([field]=False) will count both the True and False results of the inner boolean condition since a) the definition of COUNT() is to count every non-Null value and b) both boolean True and False are non-Null. If there are no Null values of [field] then these methods are effectively the same as using SUM([Number of Records]).

                 

                If [field] is a Boolean value then what I'll use is SUM(INT(NOT [field])), though that isn't as obvious as something like SUM(IF NOT [field] THEN 1 ELSE 0 END).

                 

                If [field] is a string value then I'll use SUM(IF [field] = 'false' THEN 1 ELSE 0 END).

                 

                Here's an example using a data set where Product ID has values from 1-5 with no Null values:

                 

                Screen Shot 2017-10-02 at 2.29.53 PM.png

                 

                Jonathan

                3 of 4 people found this helpful
                • 5. Re: Count (Expression = True)
                  Chris McClellan

                  Just remember that it's "quotes" not "coats" - English is a terrible language at times

                  • 6. Re: Count (Expression = True)
                    Jayshree Dawrewar

                    Yeah that's right Chris.

                    Sorry for that, got engrossed in the issue!

                    1 of 1 people found this helpful