Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Date Difference calculation referencing grouping columns

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 20, 2020 7:26 AM
(151 views)

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" )
)
```

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

4 REPLIES 4

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 )
);
```

Highlighted
##

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Date Difference calculation referencing grouping columns

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Date Difference calculation referencing grouping columns

Thank you! This worked!