Hello, I have the following 4 tables below that lists donors, their managers (or reps), the amount of times donors have been contacted, and the donations they have made:
Donor Table
Donor Donor ID Donor Manager ID Donor Manager Type
John 0001 4 Primary
John 0001 3 Secondary
John 0001 2 Secondary
Sue 0002 1 Primary
Sue 0002 3 Secondary
Sue 0002 4 Secondary
Henry 0003 2 Primary
Mary 0004 3 Primary
Donor Manager Table
Donor Manager Donor Manager ID
Walter 1
Lisa 2
Katherine 3
Evan 4
Contact Table
Donor ID Author ID Contact Type Contact Fiscal Year
0001 4 Visit 2014
0001 4 Visit 2014
0001 1 Visit 2014
0001 1 Visit 2013
0001 1 Email 2014
0001 1 Phone Call 2012
0002 2 Visit 2014
0002 1 Visit 2014
0003 3 Visit 2013
0003 3 Phone Call 2014
0004 3 Phone Call 2014
0004 4 Visit 2014
Donation Table
Donor ID Receipt # Fiscal Year Donation
0001 01 2012 $100
0001 02 2014 $200
0002 03 2013 $300
0002 04 2014 $400
0002 05 2014 $600
0003 06 2014 $800
0004 07 2012 $1000
0004 08 2014 $1300
I want to know the total donations for donors who were:
A. Visited by their Primary Managers in a particular fiscal year (where contact type = "Visit" and Donor Manager Type = "Primary")
B. Visited by anyone in a particular year
So for example if looking for total donations in FY2014 based on visits in that same year the results should look like this:
Donor Manager Ttl $ from Donors Visited by Primary Ttl $ from All Donors Visited
Walter $1000 (Walter is primary for Sue) $1200 (Walter visited Sue and John in 2014)
Evan $200 (Evan is primary for John) $1500(Evan visited John and Mary in 2014)
The particular fiscal year is a 4-digit variable that can be inputted called vContactFiscalYear. This would look for contacts as well as donations in that year.
I'm trying to use the aggregate function similar to below, but it's not correct as it totals $0, especially at the point when I enter the logic that the author id = donor manager id:
=SUM(AGGR(SUM(DISTINCT IF(ISNULL(DONATION), 0, IF(CONTACT_FISCAL_YEAR = vContactFiscalYear AND CONTACT_TYPE = 'Primary' AND AUTHOR_ID = DONOR_MANAGER_ID AND FISCAL_YEAR = vContactFiscalYear, DONATION, 0))), RECEIPT_NUMBER))
Note that some donors may not have been visited in the queried year as well so hopefully the calculation would be able to incorporate potential null values as well.
