Calculating days between matching rows and return the difference

Svend

Hi all,

 

I'm trying to calculate the number of days between the previous donation and the next donation, sorted by each donor ID. If the Donor only made 1 donation, the difference should be blank. If the Donor made multiple donations, then each row should return the number of days lapsed (except for the first donation made).

 

I tried using the formula "Dif( :Donation Received Date ) / 86400" but it doesn't account for the different Donor ID.

 

Appreciate if anyone can give me guidance on how to use the conditional statement in the formula editor or via scripting. Thank you.

 

 

Regards,

Svend

txnelson

Here is a formula that I think will do the trick

returned = .;
If( Row() > 1,
	If( :Donor ID[Row()] == :Donor ID[Row() - 1],
		returned = (:Donation Received Date[Row()] - :Donation Received Date[Row() - 1] )
			/ in Days(1),
		returned = .
	)
);
returned;