Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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)
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;
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