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

# Count (Expression = True)

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)

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)

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)

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)

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:

Jonathan

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

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

• ###### 6. Re: Count (Expression = True)

Yeah that's right Chris.

Sorry for that, got engrossed in the issue!

1 of 1 people found this helpful