JSL Calculate Elapsed Time Within Column By a Variable

[#1]

Hello,

I am wondering if it's possible to create a JSL column formula to calculate the number of days between the observation of an ID WITHIN the same DATE column (e.g. "By ID, Calculate elapsed days By DATE").  The output would evaluate the appearance(s) of the same ID and calculate the elapsed time between each appearance of the same ID.  This is not a column A to column B comparison, this is a Within column A evaluation (within same column).

Note, the First observation of any ID will be blank because there is no comparison (first observation).  After First observation, the calculations would function 1st vs. 2nd ... 2nd vs 3rd ... and so on.

Below is a draft concept.  Thank you for any help.

ID	DATE	ELAPSED_DAYS
ABC123	1/1/2021
ABC123	10/31/2021	303
ABC123	6/30/2023	607
EFG567	6/30/2021
EFG567	10/31/2021	123
XYZ901	5/31/2021

[#2]

Use Col Min() and Date Difference. If you really need the first observation to be missing value, you can add if statement to check for it

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("ID",
		Character,
		"Nominal",
		Set Values({"ABC123", "ABC123", "ABC123", "EFG567", "EFG567", "XYZ901"})
	),
	New Column("DATE",
		Numeric,
		"Continuous",
		Format("m/d/y", 12),
		Input Format("m/d/y"),
		Set Values([3692304000, 3718483200, 3770928000, 3707856000, 3718483200, 3705264000])
	),
	New Column("ELAPSED_DAYS",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([., 303, 607, ., 123, .])
	)
);

dt << New Column("elapseddays", continuous, numeric, Formula(
	Date Difference(Col Min(:Date, :ID), :Date, "day"))
);

[#3]

Jarmo,

Thanks, if I interpret correctly the Col Min function in your formula would establish the first observation (earliest date) of the ID.

That can be helpful for a total time between first observation and "nth" observation.

However, I would also like to calculate most recent observation vs. previous observation (elapsed time between each observation).  Such as "nth" observation vs "nth - 1" observation.  In example below, "row 2 vs row 3" is an elapsed time of 607 days (second observation of ID vs. third observation of ID).

Any thoughts?  Your feedback is appreciated.  Thank you.

[#4]

This formula might look a bit complicated, but it is using Col Moving Average to get previous value for ID

dt << New Column("elapseddays_new", continuous, numeric, Formula(
	Date Difference(Col Moving Average(:Date, 1, 1, 0, :ID) * 2 - :Date, :Date, "day"))
);

Other good option would be to use Lag() and if statement to check if ID has changed

[#5]

Probably easiest if split your date column by ID, then do all your date math in each of the new columns and then stacking it all again.

[#6 · Accepted solution]

If you must do it in a column formula then you need a local variable. Use this column jsl:

New Column( "Elapsed_days",
	Numeric,
	"Nominal",
	Format( "Best", 9 ),
	Formula(
		Local( {ResetRow = 1},
			If(
				Row() == 1, .,
				:ID != Lag( :ID ), ResetRow == Row(),
				(:Date - Lag( :Date )) / In Days( 1 )
			)
		)
	)
)