9 Replies Latest reply on Dec 12, 2011 8:17 PM by Joe Mako

# when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

i have a large donor base where donors can split their donation between several causes.

for example a donor  may contribute \$10,000, split between these causes:  \$3,000 animals, \$1,000 building, \$6,000 unrestricted.

our database returns each  donation per cause. not the \$10,000. so i cannot find the max unallocated total donation, only the max from all the cause donations.

i need to find for any time period the donor with max total donation, irrespective of the allocation to causes.

i know there is a simple solution - any ideas?

thanks

• ###### 1. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

It is not clear to me from your description what you are looking for. Attached are two views. Can you be more specific with what you want for a result?

• ###### 2. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

i need to know the donor with the maximum total donation during any given period. and the donor with the max donation to a specific cause. thanks

• ###### 3. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

correction/clarification:

i need to know the donor with the maximum total single transaction donation during any given period. and the donor with the max single transaction donation to a specific cause. thanks

• ###### 4. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

I added some more data so there could be more than one period.

• ###### 5. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

can you provide a brief explanation of what you did? thanks

• ###### 6. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

for the sheet "Donor Transaction", I created a set of "donor" and "transaction" sorted this pill descending on the Sum of "donation". I then added the fields "Period and "donor". I created a calculated field called "Most" with a formula of

`INDEX()==1`
and placed it on the filter shelf setting it to keep only when True, and set its Compute using to use the set "Donor Transaction". This causes it to partition on Period and address on the combinations of "donor" and "transaction", keeping only the one with the highest sum of donation.

Same concept for the other one but "cause" was needed for both partitioning and addressing so I duplicated it so it could do both.

See http://www.tableausoftware.com/support/knowledge-base/nestedsorting for more details.

• ###### 7. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

can you explain the formula of INDEX()==1

• ###### 8. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

can you explain the formula of INDEX()==1

• ###### 9. Re: when the donor splits their single gift among several causes, need to find the max total donation per donor per transaction (sum

INDEX() is a very useful function, here is a good description of it:

INDEX can be looked at as the "rank" function. (See other rank articles.) "Compute Using" defines the partition within which INDEX will count from 1 to N.

You can think of it as an incremental number that counts things. In this case INDEX()==1 is saying keep the first, this would be the same as FIRST()==0. The compute using is the concatenation of Donor Transaction, so it is counting the distinct combinations of those dimensions within each Period. Donor and Transaction are being used for partitioning, and Period is partitioning. Addressing is what it will count along, and partitioning is what will cause a full restart of the counting.

If this does not help, can you be more specific with your question?