Summary of largest annual donation

Lindsay Godman

I am working on a  GIANT export for moves management this year. We are wanting to see which year a constituent donated the most (summary). Is there a way to do this (easily)?


We have RE:Search module (but haven't used it) if that helps.

Alan French

I'm a bit late to the party here - did you manage to resolve this in the end? The closest canned report I can see might be the First/Greatest/Latest Gift Report, but that seems to only look at individual gifts rather than a summary per year.


The only thing that springs to mind would be to do a pivot report based on a query of all gifts, with their dates, amounts and constituent IDs, so you end up with a row per constituent and a column per year. If you want to use financial years rather than calendar years, you might need to add an extra column to your data with a formula to determine which year a date falls into.


Once you've got a summary of giving by year, you can then use a combination of the Index, Match and Max functions to determine in which year each person gave the most. For example, if I had the below data in columns A:E, the formula =INDEX(B$1:E$1,MATCH(MAX(B2:E2),B2:E2,0)) would tell me in which year donor 123456 gave the most.

2015 2016 2017 2018 £10 £55 £125 £40 £5 £15 £0 £10

Donor ID

123456

654321

(Happy to step through the formula in more detail, but basically it's using a Max to determine what the maximum amount was, then using a Match to determine which column that was in, then using an Index to retrieve the year from row 1 of the same column)


Waiting for someone to come along and tell me there's a canned report that does all this automatically! ?

Lindsay Godman

I'll try that out. Thanks!