Formula Help

Hi Amanda,

I think you could use some Venn math to get at the numbers, if I understand your question. Quick example: With constituents in groups A and B, the unique count of constituents in ONLY group B = the unique count in the full set of A or B, minus the unique count of constituents in all of A.

Assuming a hierarchy of including constituents where if they're in the Online bucket AND the Phone bucket, count them only in Online. And similarly, Phone and Mailed, count them in only Phone. Then the count of distinct constituents in Phone is going to be (the count of distinct constituents in Online or Phone), minus (the count of constituents in Online). The count of constituents in Mailed then would be (the count of constituents in Online or Phone or Mailed) minus (the count of constituents in Online or Phone).

Actually implementing this with your particular filter sets would need to look something like this...

Online - just as you have it already actually - the unique count of constituents with those filters.

Phone - we probably can't simply add more inclusions into the filter for constituent counts because it's the unique combination of those three filters that designate Online and Phone. Not that you have data like this, but for example, we wouldn't want to count Web Subs with Invoice payment methods. So, to get the unique count of constituents, we need to group by Constituent ID in our formula and in essence, flag up constituents when they meet the unique filter criteria for Online or Phone. What follows says, for each Constituent ID, if the count of unique transactions for Online is > 0 or for Phone is > 0, then return the Constituent ID for that Constituent ID to the COUNT() function at the top of the formula, else return an empty value NULL. So if the same constituent is in both the Online AND Phone filter sets, it will be counted only once by this formula.

COUNT ( [Constituent ID] ,
IF ( ( [# of unique Transaction ID] , [Mode of Sale] , [Payment Method Type] , [Batch Owner] ) > 0
   OR ( [# of unique Transaction ID] , [Mode of Sale1] , [Payment Method Type1] , [Batch Owner1] ) > 0
   , MAX([Constituent ID]) , NULL
)

Then subtract from that what is probably your existing Phone formula, which will look, altogether, something like this:

COUNT ( [Constituent ID] ,
IF ( ( [# of unique Transaction ID] , [Mode of Sale] , [Payment Method Type] , [Batch Owner] ) > 0
   OR ( [# of unique Transaction ID] , [Mode of Sale1] , [Payment Method Type1] , [Batch Owner1] ) > 0
   , MAX([Constituent ID]) , NULL
)  -
( [# of unique Constituent ID] , [Mode of Sale] , [Payment Method Type] , [Batch Owner2] )

Repeating that process for Mailed, the first half of the formula for the unique count of constituents with Online or Phone or Mail looks like this:

COUNT ( [Constituent ID] ,
IF ( ( [# of unique Transaction ID] , [Mode of Sale] , [Payment Method Type] , [Batch Owner1] ) > 0
OR ( [# of unique Transaction ID] , [Mode of Sale1] , [Payment Method Type1] , [Batch Owner2] ) > 0
OR ( [# of unique Transaction ID] , [Mode of Sale2] , [Payment Method Type2] , [Batch Owner3] ) > 0
, MAX([Constituent ID]) , NULL
)
)

And then to subtract the unique count of constituents with Online or Phone looks like the first half of the Phone formula above, so:

COUNT ( [Constituent ID] ,
IF ( ( [# of unique Transaction ID] , [Mode of Sale] , [Payment Method Type] , [Batch Owner1] ) > 0
OR ( [# of unique Transaction ID] , [Mode of Sale1] , [Payment Method Type1] , [Batch Owner2] ) > 0
OR ( [# of unique Transaction ID] , [Mode of Sale2] , [Payment Method Type2] , [Batch Owner3] ) > 0
, MAX([Constituent ID]) , NULL
)
) -
COUNT ( [Constituent ID] ,
IF ( ( [# of unique Transaction ID] , [Mode of Sale] , [Payment Method Type] , [Batch Owner1] ) > 0
OR ( [# of unique Transaction ID] , [Mode of Sale1] , [Payment Method Type1] , [Batch Owner2] ) > 0
, MAX([Constituent ID]) , NULL
)
)

Best,
Chris

Hi Chris, 

I am getting a Function Syntax Error: Unexpected end of formula with this. What am I doing wrong? 

Phone:the count of distinct constituents is going to be (the count of distinct constituents in Online or Phone), minus (the count of constituents in Online). 

COUNT( [Constituent ID] ,
IF(([# of unique Transaction ID],[Mode of Sale2],[Payment Method Type1],[Batch Owner3])>0
OR([# of unique Transaction ID],[Mode of Sale1],[Payment Method Type],[Batch Owner2])>0
,MAX([Constituent ID]),NULL
)-
([# of unique Constituent ID],[Mode of Sale2],[Payment Method Type1],[Batch Owner3])

Thanks!

Amanda 

Hi Amanda,

Probably just the final closing ")" at the very end that links up to the one right after "COUNT". When you put the cursor before an opening parenthesis, the formula editor does highlight the the closing one, and vice versa, when the cursor is placed just after a closing parenthesis, the editor highlights the corresponding opening one.