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!

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.

Chris Wallingford,

So, I'm giving the following approach a try.

SUM ( [Contribution ID] ,
           IF ( ( ( [Total Amount] , [Fiscal Year to Date Flag] ) + ( [Total Amount] , [Days Since Campaign Start] ) ) >= 400
             AND ( ( [Total Amount] , [Fiscal Year to Date Flag] ) + ( [Total Amount] , [Days Since Campaign Start] ) ) < 700
           , ( ( [Total Amount] , [Fiscal Year to Date Flag] ) + ( [Total Amount] , [Days Since Campaign Start] ) )
           , NULL )
    )

This appears to be giving me Fiscal Year to Data (including early giving to a campaign) between $400 and 699.99 for a particular customer.

I can do the count by replacing the "then"  on line 4 of the formula with a 1.

Then the next thing to consider is a comparison between Fiscal Years.  How much ahead or behind are we from prior years. 

P.S. How do you get those nice code block around your formulas here on TessituraNetwork.com.  Mine came out a bit wonky.  

Chris Wallingford

Turns out that my formula is not quite right.  In the future year, I'm doubling the money somehow.

The fiscal Year to Date Flag is Text contains 'Y'

The Days Since Campaign Start in < 0

Not yet clear what I've got wrong with this.

--Tom

Turns out that my last formula above definitely does not work for future years.  The fiscal year-to-date flag is causing problems in future years.   Where Days Since Campaign Start date is negative and in some cases the fiscal year-to-date flag is Y.  Have to go back to the drawing board I think. 

Second I've noticed that these formulas are also getting complicated enough and numerous enough that recalculate times are better described in minutes than in seconds.  (We are on RAMP)

Hi Tom,

I read this formula as... For each contribution (not each constituent), if the total amount any contribution date's fiscal year to date, plus the total amount where the contribution was before the campaign start date is between 400 and 699.99, then return that value to the outer sum. This implies that all your campaign start dates are set to the fiscal year start date of the campaigns' fiscal years, otherwise there are contributions after the FY start and before the campaign start.

Assuming this is grouped by Campaign Fiscal Year in a Pivot widget, this might be phrased as contributions that are FYTD or the fiscal year of the contribution date is before the fiscal year of the campaign

• Contribution Date Fiscal Year to Date = Y
• OR [Max Contribution Date Fiscal Year] < [Max Campaign Fiscal Year] 

I'm going to give you an alternative (better, hopefully) approach in a moment, but this is the more versatile, from a formula maintenance perspective, requiring no hardcoded filters. To get the bucketing up to a constituent level, it still needs to evaluate the FY of each contribution rather than the [Max Contribution Date Fiscal Year] within the constituent. 

SUM ( [Constituent ID] ,
  IF (
    ( [Total Amount] , [Contribution Date Fiscal Year to Date Flag] )
    + 
    SUM ( [Contribution ID] ,
      IF (
        ( [Max Contribution Date Fiscal Year] < [Max Campaign Fiscal Year] )
        , [Total Amount]
        , NULL
      )
    ) >= 400
 AND 
   ( [Total Amount] , [Contribution Date Fiscal Year to Date Flag] )
   + 
   SUM ( [Contribution ID] ,
     IF (
       ( [Max Contribution Date Fiscal Year] < [Max Campaign Fiscal Year] )
       , [Total Amount]
       , NULL
     )
   ) < 700
 , 
   ( [Total Amount] , [Contribution Date Fiscal Year to Date Flag] )
   + 
   SUM ( [Contribution ID] ,
     IF (
       ( [Max Contribution Date Fiscal Year] < [Max Campaign Fiscal Year] )
       , [Total Amount]
       , NULL
     )
   ) 
 , NULL
 )
) 

This is an intensive, expensive, repetitive query that interrogates each constituent and contribution over several fiscal years, and processes those amounts across multiple aggregations. We can eliminate one of those aggregation passes though by being a little more prescriptive. Let's say we only need to see the current and prior 2 fiscal years. Within each fiscal year we can take that same Amount to Fiscal Date and using a CASE statement add any contributions where if the campaign is 2022, the contribution date fiscal year is from 2021 or 2020... explicitly, not relatively. 

SUM ( [Constituent ID] ,
 IF (
 ( [Total Amount] , [Fiscal Year to Date Flag] ) + CASE 
 WHEN [Max Campaign Fiscal Current Year Offset] = 0 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < 0] )
 WHEN [Max Campaign Fiscal Current Year Offset] = -1 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < -1] )
 WHEN [Max Campaign Fiscal Current Year Offset] = -2 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < -2] )
 END >= 400
 AND
 ( [Total Amount] , [Fiscal Year to Date Flag] ) + CASE 
 WHEN [Max Campaign Fiscal Current Year Offset] = 0 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < 0] )
 WHEN [Max Campaign Fiscal Current Year Offset] = -1 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < -1] )
 WHEN [Max Campaign Fiscal Current Year Offset] = -2 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < -2] )
 END < 700
 ,
 ( [Total Amount] , [Fiscal Year to Date Flag] ) + CASE 
 WHEN [Max Campaign Fiscal Current Year Offset] = 0 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < 0] )
 WHEN [Max Campaign Fiscal Current Year Offset] = -1 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < -1] )
 WHEN [Max Campaign Fiscal Current Year Offset] = -2 THEN ( [Total Amount] , [Cont Fiscal Current Year Offset < -2] )
 END
 ,
 NULL
 )
) 

I get the same results with both of the above approaches, so whichever is the more performant is probably the way to go.

As for that formatting hint...

See you all in a few minutes.