Sum of first and second donations

[Anonymous]

Donor,    DonationDate,    DonationAmount

A,        01/01/2016,      20.00

A,        01/02/2016,      10.00

A,        01/03/2016,      40.00

A,        01/04/2016,      150.00

B,        28/02/2016,      33.00

B,        30/03/2016,      55.00

B,        30/04/2016,      11.00

C,        15/03/2016,      12.00

I would like add three expressions to a chart that show:

The number of Donors that made a second donation

The total of first donations

The total of second donations

So for the data above it would look like this:

Donors with 2nd donation     Total 1st Donations    Total 2nd donations

2                                         67.00                        65.00

I think it is possible using the aggr function, but I am not sure how.

Thanks

David

[Janneke]

Hi David,

You can easily do so by adding a field with DonationNumber.

For the first expression you then count the amount of number 2's: sum(if(DontationNumber=2,1,0))

For the second expression: sum(if(DonationNumber=1,DonationAmount,0))

For the third expression: sum(if(DonationNumber=2,DonationAmount,0))

Good luck!!

Janneke.

[Anonymous]

Hi Janneke.

Thanks for your response.

Unfortunately I need this to work within other selections.

For example there is also a donation type, which can have values such as Cash and DirectDebit.

So if type Cash is selected I would need to show the total of  1st and 2nd Cash gifts.

David

[Clever_Anjos]

As janneke_‌ suggested I would create a flag using something like:

LOAD *,
Autonumber(DonationDate,Donor) as Flag

resident yourtable
order by DonationDate;

So your expressions would be:

The number of Donors that made a second donation

COUNT(distinct {<Flag={"2"}>}Donor)

The total of first donations

Sum( {<Flag={"1"}>} DonationAmount)

The total of second donations

Sum({<Flag={"2"}>} DonationAmount)

[Pulkit_Thukral]

Hi David,

Please find attached link for complete qvw : In Reference to : Sum of first and second donations

Attaching qvw file and the sample data excel along with this.

Hope the requirement is met. !

Script:

abc:

LOAD Donor,

     DonationDate,

     DonationAmount

FROM

HaasMachines.xlsx

(ooxml, embedded labels, table is Sheet4);

NoConcatenate

mno:

load Donor,

if(count(Donor) > 1,1,0) as count

Resident abc

Group by Donor;

NoConcatenate

rpy:

load Donor,

sum(count) as Multiple_Donation_Servers

Resident mno

Group by Donor;

drop Table mno;

NoConcatenate

Table:

LOAD Donor,

  Date(Min(DonationDate)) as DonationDate2,

  FirstSortedValue(DonationAmount, DonationDate) as DonationAmount2

Resident abc

Group By Donor;

NoConcatenate

Table2:

LOAD Donor,

  Date(Max(DonationDate)) as DonationDate3,

  FirstSortedValue(DonationAmount, -DonationDate) as DonationAmount3

Resident abc

Group By Donor;

DROP Table abc;

[Anonymous]

Hi Pulkit

I can't download your document because you are in a private group.

Do you think your solution will work within the selections I mentioned earlier?

Thanks

David