cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ktbrickey
Level II

Date Difference calculation referencing grouping columns

I am trying to calculate a Date Difference in Days between the current row and the last row where a comment is non-missing. Any help greatly appreciated!

 

If( (:ID == Lag( :ID ) | Row() == 1) & !Is Missing( :Comments ), 0,
Date Difference( :Sampled Date, Lag(:Sampled Date, n=last row where !Is Missing(:Comments)), "Day" )
)

 

ktbrickey_0-1589984249701.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Date Difference calculation referencing grouping columns

Here is the formula that I would use for the calculation

If( Row() == 1 | :ID != Lag( :ID ),
	lastCmtDate = .
);
If( :Comments != "",
	lastCmtDate = :Sampled Date
);
:Days Since Last Comment = (:Sampled Date - lastCmtDate) / In Days( 1 );
Jim

View solution in original post

7 REPLIES 7
ih
Super User (Alumni) ih
Super User (Alumni)

Re: Date Difference calculation referencing grouping columns

If the table is always sorted by date you could do this with two columns, as shown below.

New Column( "Last Comment Date",
	Numeric,
	"Continuous",
	Format( "m/d/y h:m", 19 ),
	Input Format( "Monddyyyy h:m" ),
	Formula(
		If(
			Row() == 1, :Sampled Date,
			:Comments == Lag( :Comments, 1 ), Lag( :Sampled Date, 1 ),
			:Sampled Date
		)
	),
	Set Display Width( 136 )
);
New Column( "Days Since Last Comment New",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( (:Sampled Date - :Last Comment Date) / (3600 * 24) ),
	Set Display Width( 109 )
);
ktbrickey
Level II

Re: Date Difference calculation referencing grouping columns

Thanks ih, but this doesn't work. The new column you created is not populated with the date of the last comment, just the lag sampled date. Thanks for trying!

txnelson
Super User

Re: Date Difference calculation referencing grouping columns

Here is the formula that I would use for the calculation

If( Row() == 1 | :ID != Lag( :ID ),
	lastCmtDate = .
);
If( :Comments != "",
	lastCmtDate = :Sampled Date
);
:Days Since Last Comment = (:Sampled Date - lastCmtDate) / In Days( 1 );
Jim
ktbrickey
Level II

Re: Date Difference calculation referencing grouping columns

Thank you! This worked!

voy-voy
Level II

Re: Date Difference calculation referencing grouping columns

I think I have a very simular problem. 

I tried the formula you give us, but in my table it don't work. (column 5 (Differenz in Tagen Bauteil 1)).

 

At first it is write the day difference not in the rows wear the comment (1) is, rather in all other rows.

And more important for me, it's write just the difference to the last date, and not to the date where the last comment (or no comment) was.

 

Could you please help, again?

txnelson
Super User

Re: Date Difference calculation referencing grouping columns

Try this

If( Row() == 1 | :Source Table != Lag( :Source Table ),
	lastCmtDate = :"Datum:"n,
	lastCmtDate = Lag( :"Datum:"n )
);
If( :Bauteil 1 == "",
	:Differenz in Tagen Bauteil 1 = (:"Datum:"n - lastCmtDate) / In Days( 1 ),
	:Differenz in Tagen Bauteil 1 = .
);
Jim
voy-voy
Level II

Re: Date Difference calculation referencing grouping columns

Thank you Jim. 

It is still not work, bu with your formulars I have found now a Work around and use 2 new colums to calculated and now it works :)

New Column( "Letzter Komentar Tag",
Numerisch,
"Stetig",
Format( "t.m.j", 12 ),
Eingabeformat( "t.m.j" ),
Formel(
If( Row() == 1 | :Quelltabelle != Lag( :Quelltabelle ),
lastCmtDate = .
);
If( :Bauteil 1 == "1",
lastCmtDate = :"Datum:"n,
lastCmtDate
)));

New Column( "Differenz Bauteil 1",
Numerisch,
"Stetig",
Format( "Bestes", 12 ),
Formel(
If( :Bauteil 1 == "1",
(:"Datum:"n - Lag( :Letzter Komentar Tag )) / In Days( 1 )
)));