Help Pulling Report - Total # of Gifts, Total Amount of Gifts, Average Gift for 2015, 2016, 2017

Options
Hello! 

My boss wants me to pull a report for each of the fiscal years 2015, 2016, 2017 that include thet total number of gifts, total amount of gifts and the average gifts. She doesn't need donor names or anything. Just the numbers. I can't seem to find a Report or Query that will easily give me this information. Can anyone help?


Thank you!! 

Melissa 

Comments

  • Hi Melissa,


    This kind of question is one of the most asked on these forums, along with how to remove Query duplicates. I'd suggest a search of this forum with key words "gift query". Those results should get you a long way down the track.


    Another option I'd suggest is to use direct SQL which can often be cleaner and more flexible.


    Cheers,

    Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions


     
  • I would use a Gift Query and then export to Excel.  Include your entire date range (all years) in the criteria.


    In this case, provided you are not including other fields in your Query, you shouldn't get any duplicates.  If you are getting duplicates, build an Export based on the Query to eliminate them (just saves a step and some time if you don't need the Export).


    Once in Excel, create a new tab for your results and use formulas to get your numbers.


    If you're not familiar with Excel formulas (and since I just spend a big chunk of my day doing essentially this very thing), these formulas should work:

    Data Tab:

    paste your RE data on this tab

    for this example, your Date should be in column B and the Amount should be in column C

    cell A1: =IF(B1<>"",YEAR(B1),"")  ...skip row 1 if you have headers

    cell A2: =IF(B2<>"",YEAR(B2),"")

    etc.

    (a trick is to type the formula in the first cell, then press and hold Ctrl+Shift+DownArrow, release and then press Ctrl+D...this will copy the formula down the column)

    Results Tab:

    cell A1: 2018

    cell B1: =SUMIF(Data!$A$2:$A$65536,A1,Data!$C$2:$C$65536)

    cell C1: =COUNTIF(Data!$A$2:$A$65536,A1)

    cell D1: =AVERAGEIF(Data!$A$2:$A$65536,A1,Data!$C$2:$C$65536)

    repeat for each year that you need...


    Hope this helps!
  • Oops...I should add that if your Fiscal Year is not a Calendar Year, the formula on the Data Tab won't work properly.  Instead, you'll want to use this (for a Jul to Jun FY...for something else, change the 6 & 7 to the appropriate months):

    cell A1: =IFS(B1="","",MONTH(B1)<7,YEAR(B1),MONTH(B1)>6,YEAR(B1)+1)
  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic
    This may or may not have all the info you need. It's quick and fairly easy for a quick overview. I didn't know this function was in RE for years. Check it out at least.


    Go to Records > Funds - select your fund > on that pop-up select View > Summaries > Fund Summary - this opens up filters so you can filter for a particular year - be sure to select the gift types you want included, it defaults. It will generate an overview with average, median, total gifts, total donors, mode and goal stats. There's top donor info and a whole variety of breakdowns like by year, appeal, campaign, solicitor and more. Can be handy for quick overview.


    Has same function for Canpaigns and Appeals.


    Just a thought.
  • Marie Stark
    Marie Stark ✭✭✭✭✭
    Ancient Membership Facilitator 3 Name Dropper Photogenic

     

    I would create a constituent export. Under Summary information, Gifts, you can pull the Total number of gifts, gift amount, and average gift amount.

    In your criteria you can select the dates you need. You can pull one summary for each fiscal year.
  • Melissa Walker:

    Hello! 

    My boss wants me to pull a report for each of the fiscal years 2015, 2016, 2017 that include thet total number of gifts, total amount of gifts and the average gifts. She doesn't need donor names or anything. Just the numbers. I can't seem to find a Report or Query that will easily give me this information. Can anyone help?


    Thank you!! 

    Melissa 

    You can use Demographic and Statistical Reports->Comparison and Summaries Report. Under the Fields tab you can choose the Field to use and the Columns to print (Number of donors, Number of Gifts, Total Given, etc.) Under the Report Periods tab you can choose the start and end dates for you reporting periods.

     

Categories