Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Svend
Level I

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.

 

Pic.PNG

 

Regards,

Svend

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

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;
Jim

View solution in original post

4 REPLIES 4
Highlighted
txnelson
Super User

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;
Jim

View solution in original post

Highlighted
Svend
Level I

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?

Highlighted
txnelson
Super User

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

Jim
Highlighted
Svend
Level I

Re: Calculating days between matching rows and return the difference

Thanks a lot for your help. The formula works.

Article Labels

    There are no labels assigned to this post.