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.