Hello, hope I can illustrate this correctly. Apologies if the table skews in the display:
See the example table here:
Donor ID Receipt # Family ID Donor Name Rep Donation Legal Donor?
0001 1 01 Sarah Adam $100 Y
0002 1 01 John Adam $100 N
0003 2 02 Steve Liz $200 Y
0004 3 03 Jen Liz $1000 N
0005 3 Jen's Org $1000 Y
0006 4 04 Bill Adam $100 Y
In our database sometimes donors are grouped together under a Family ID and when that happens, they are assigned a Rep.
Also sometimes donors who make the donations want to give credit acknowledgements to their spouses or other entities so the latter are also recognized as donors. When this is the case the donors share the same receipt# and there is a column that denotes the legal donor, the person who actually made the payment, from the person who is to receive acknowledgement of the donation (this is the receipt# and legal donor column above).
As a third point, sometimes the legal donor giving credit may not have or share the Family ID. This can be seen in the last row of the example above.
In our database, the tables that hold Rep vs. Family IDs vs. Donor IDs vs. Receipt#_Donation are separate and the above is what displays when joining the tables. I want to be able to total the donations so that the calculation is intelligent enough to sum donations of the legal donor if they belong to the Rep (e.g. Adam's total would be derived from Sarah, not John), yet know when to seek out the legal donor if it is for the same receipt but the legal donor does not share the same rep (e.g. As Liz is Jen's rep, it calculates Jen's Org even though Liz does not represent the org).
Using SUM DISTINCT Donations won't help as I would not want it to exclude Bill's donation.
So the total should essentially read like the following:
Total for Adam's Donors: $200
Total for Liz's Donors: $1200
As I am still a novice with Qlikview, anything that can show the formula that I can use in a pivot table, that will display the total above would be helpful. A sample qvw file would be even better!
Thanks
