Filtered Count

[waldopepper]

Bit of a mental block here - I have a formula as follows:

distinctcount({MemberMaster.MemberNumber})

And I want to 'filter' this count by another variable e.g. only provide a count where variableB = "Y" - how do I do this?

So if the count if 50,000 at the moment and there are 10,000 records with the value of "Y", I want this count to show 40,000.

thanks
W

 

[hilfy]

You'll use two formulas:

{@CountIt}
If variableB = "Y" then {MemberMaster.MemberNumber}

{@Count}
Sum({@CountIt})

-D

 

[lbass]

You can't use a distinctcount on hilfy's first formula (and you don't want a sum of the member number), since for values <> "Y" you would get a count of 1 also, and you can't even just subtract 1, since it would possible for all instances to be "Y". So...

Your use of distinctcount implies that you might have more than one member number with a "Y" code, but you only want to count it once. It's probably best to use a running total in this case. Select {MemberMaster.MemberNumber}, distinctcount, evaluate based on a formula:

{table.field} = "Y"

Reset never.

Your post is confusing, because you say you want to count members with a value = "Y" and yet you show a sum that would reflect a count of members with a value <> "Y".

-LB