Creating Customer Groups on the fly.

Hi Tom,

I happen to have something like this handy...

In the context of a Pivot widget, the FY and Ranges will need to be... pivoted... from what you have above because we can't at this time render the Value fields on Rows. A multipass formula that looks at total giving for each constituent prior to returning results could return results only for constituents whose total giving is within a specified range:

SUM ( [Constituent ID] ,
   IF ( [Total Amount] >= 0 AND [Total Amount] < 50 , [Total Amount] , NULL )
)

Replacing the "if true" output of [Total Amount] with [Total Contribution Count] (or just "1" if that Count is a count of constituents) will yield the Count value.

Hey Chris,

We're working on a similar bit of multi-passing. We're trying to construct formulas to look at LYBNT (Last Year But Not This Year) giving. Our first formula is this, which seems to be working:

IF (

( ([Total Amount] , [Last year] ) > 0)

AND

( ([Total Amount] , [This year] ) = 0) ,


([Total Amount] , [Last year] ) , NULL


)

AMOUNTs = 'Contribution Amount'

This formula returns values, and we're checking them now to be sure. We can then filter this to be greater than 0, to weed out any null values in the table.

When we try to fine-tune this by removing the 'membership' fund, however, it breaks down. The formula still parses, but it gives 0 for everyone. This second formula is:

IF (

( ([Total Amount] , [Fund NOT member] , [Last year] ) > 0)

AND

( ([Total Amount] , [Fund NOT member] , [This year] ) = 0)

,

([Total Amount], [Fund NOT member] , [Last year] ) , NULL


)

Why would this second formula not return anything? I would appreciate your thoughts!

Hey Chris,

We're working on a similar bit of multi-passing. We're trying to construct formulas to look at LYBNT (Last Year But Not This Year) giving. Our first formula is this, which seems to be working:

IF (

( ([Total Amount] , [Last year] ) > 0)

AND

( ([Total Amount] , [This year] ) = 0) ,


([Total Amount] , [Last year] ) , NULL


)

AMOUNTs = 'Contribution Amount'

This formula returns values, and we're checking them now to be sure. We can then filter this to be greater than 0, to weed out any null values in the table.

When we try to fine-tune this by removing the 'membership' fund, however, it breaks down. The formula still parses, but it gives 0 for everyone. This second formula is:

IF (

( ([Total Amount] , [Fund NOT member] , [Last year] ) > 0)

AND

( ([Total Amount] , [Fund NOT member] , [This year] ) = 0)

,

([Total Amount], [Fund NOT member] , [Last year] ) , NULL


)

Why would this second formula not return anything? I would appreciate your thoughts!

Hi Nathanael,

You might be running into a bit of luck that the first formula is working as expected. When a constituent doesn't have a contribution [this year], then that returns NULL, and evaluating whether NULL = 0 returns FALSE, which makes the IF false. So if you're eliminating $0 contributions with a widget filter, you can just evaluate whether ISNULL [this year]:

SUM ( [Constituent ID] ,
 IF ( 
 ( [Total Amount] , [Campaign Fiscal Current Year Offset = -1] ) > 0 
 AND ISNULL ( ( [Total Amount] , [Campaign Fiscal Current Year Offset = 0] ) ) 
 , ( [Total Amount] , [Campaign Fiscal Current Year Offset = -1] ) 
 , NULL
 )
 )

Alternatively, if not filtering the widget/dashboard to excluded $0 contributions, then adding that to the formula looks like this:

SUM ( [Constituent ID] ,
 IF ( 
 ( [Total Amount] , [Campaign Fiscal Current Year Offset = -1] ) > 0 
 AND ( ( [Total Amount] , [Campaign Fiscal Current Year Offset = 0] ) = 0 
   OR ISNULL ( ( [Total Amount] , [Campaign Fiscal Current Year Offset = 0] ) ) )
 , ( [Total Amount] , [Campaign Fiscal Current Year Offset = -1] ) 
 , NULL
 )
 )

Once you add in the Fund filter, the same issue applies, and may be more prevalent, and so using ISNULL instead of = 0 may be what you need...

SUM ( [Constituent ID] ,
 IF ( 
 ( [Total Amount] , [Campaign Fiscal Current Year Offset = -1] , [Fund not Memb] ) > 0 
 AND ISNULL ( ( [Total Amount] , [Campaign Fiscal Current Year Offset = 0] , [Fund not Memb] ) ) 
 , ( [Total Amount] , [Campaign Fiscal Current Year Offset = -1] , [Fund not Memb] ) 
 , NULL
 )
 )

Great, thank you! Appreciate the clarifications.