In the Contribution Cube, I'd like to group customers by the total contribution amount year to date.
Here are some example Contributions
This list would continue and would include campaigns for which I'd want to filter
Here are some example ranges
There would be more levels. I'd also need to do this for each of 5-6 past fiscal years. Short of a bunch of manually created formulas. Is there a good way to get a summary like this done in Tessitura Analytics?
Note these are not membership levels and would have to be calculated on many past years. They are ranges that need to be calculated on the fly depending on the campaign filtering.
Anyone out there got any good ideas on how to proceed?
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 ) )
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)