79492_en-US

liliconcom

Calculating days between matching rows and return the difference

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

Re: Calculating days between matching rows and return the difference

Thanks a lot for your help. The formula works.

Re: Calculating days between matching rows and return the difference

it is a formula, not an open coded script, so paste it into the formula editor

Re: Calculating days between matching rows and return the difference

Hi Nelson,

 

Thanks for the quick assistance.

May I ask if it should be pasted into the formula editor or saved as a script to executed?

Re: Calculating days between matching rows and return the difference

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;