BusinessObjects Board

Need help with Count with WHERE clause

I have a policy HAB 000002 00 with many counties. I want to count the policy numbers where counties are (‘Dallas’, ‘Tarrant’). In this policy I have both counties present.

I have variable as follows:

=Count([Policy Number]) Where ([Risk County] InList (“Collin”;“Dallas”;“Denton”;“Tarrant” ))

When I use this variable with policy HAB 000002 00, I get the total count as ‘2’ because there are 2 counties present there.
I would to count it as 1 because I want to show there is 1 policy that have those 2 counties. How can I accomplish that.


kpiracha (BOB member since 2011-03-23)

did you try count( … ;distinct ) ?


n8aktiv :de: (BOB member since 2018-12-29)

My understanding was the count by default counts the distinct values so distinct is optional. I did try with distinct but got the same results.


kpiracha (BOB member since 2011-03-23)

try another calculation-context by moving parenthesis:

old:
=Count([Policy Number]) Where ([Risk County] InList (“Collin”;“Dallas”;“Denton”;“Tarrant” ))

new:
=Count([Policy Number] Where ([Risk County] InList (“Collin”;“Dallas”;“Denton”;“Tarrant” )))


n8aktiv :de: (BOB member since 2018-12-29)

Very nice. Worked like a charm. Thank you, sir.


kpiracha (BOB member since 2011-03-23)