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.

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...

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...