Exporting the Total Amounts, excluding Pledges

Chris Z

I'm trying to use an export of my list of constituents assigned to portfolios to get the total amounts given this fiscal year (9/1-8/31) and last fiscal year. No pledges. Yes soft credits.

 

I have a few different "categories," including Annual, Restricted, Capital and Events.

 

I don't believe I can do this by only the Fund, because we have some payments in the current fiscal year that were pledges last year. I don't want the pledge to show up in last year's total.

 

We have some people who pledge and pay in the same fiscal year.

 

As best I can figure, if I export everything for this fiscal year as the first export, and then do a second export of gifts only pay-cash gift type; then I can manually transfer the pay-cash over onto the first export. Another thing that crossed my mind was exporting the Fund Category and the gift date, I should be able to get the totals. The problem is that in the end, I'm basically having to transfer the totals to the row with the constituent (VLOOKUP).

 

This is REALLY cumbersome, and I'm trying to find a better way, as I run this twice a month. Is there a way to manipulate one of the reports (like Gift Detail and Summary) to show what I'm after?

 

If there is anyone out there who is dealing with portfolio giving totals, and on a fiscal year, I'd appreciate your insight.  Thanks.

Keri Barnhart

I'm not sure if I'm understanding your goal, but maybe it would be easier to use a Query output, rather than an Export, then run a pivot table to summarize by donor name, fund, gift officer, etc.?


We report on gifts credited to the fundraiser, rather than portfolio gifts or fund gifts which can all overlap among fundraisers here, and I simply pull the query output into excel - all gift types, fundraiser credit, and amounts. The only thing is that I have to make sure I only choose output items that are 1:1 with the fundraiser credit, so if you have any 1:many items, this wouldn't work for you. I end up running two different versions of the same report to find fund activity, because of fund splits, and it isn't linked to fundraiser credit. (a $1,000 gift can be split 3 ways for funds, 2 ways for fundraiser credit, and there isn't a way to tie the 2 fundraisers to the specific fund splits, as far as I know, so they both get credit for all 3 splits)

Chris Z

I can finally explain why exporting Summary Gift Amounts won't work:

It has to do with payments of pledges.  When I choose the Gift Summary Amount, I can choose Funds (or leave them blank) and Gift Types.  For my 2019 Annual amount, there isn't a way to say "2019 Annual & Cash" or "2018 Annual & Pay-Cash".  I don't have the ability to exclude "2018 Annual & Cash" or "2018 Annual & Pay-Cash with payment made last FY".  Since we operate on a cash basis, if someone pays an old pledge in the current fiscal year, it is counted.  But I don't want the Cash from the previous fiscal year or any payments that might have been made on a multi-year installment payment plan to be counted twice.


The best thing I found were Fund Categories on each fund.  By doing a Gift export I was able to get that field, use Pivot Tables, and get what I wanted.

Keri Barnhart

The Gift Summary field is frustrating! One of the things I have done to get around it when I need pledge balances counted with pledge payments or other complexities the Summary can't provide is to use the Constituent Giving History Report, which allows for more control over the gift filters, and export it to CSV. Then I spend time reformatting it to get me a nice pivot table so I can vlookup the amounts - the output is a list of all matching gifts by constituent.