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

- JMP User Community
- :
- Discussions
- :
- Calculate Rate of Change Grouped by Column

- 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

Mar 31, 2020 1:25 PM
(1905 views)

I, like many others I suppose, are looking at the COVID-19 data (I got mine from https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv). I would like to plot the daily change in confirmed cases by State. I've been able to work it out by first sorting the data by State, and then calculating the deltas:

```
If(:state == Lag(:state, 1),
:cases - Lag(:cases, 1)
)
```

But is there any way to do this without sorting the data first, using some kind of Group By formulation?

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

Created:
Mar 31, 2020 1:51 PM
| Last Modified: Mar 31, 2020 2:02 PM
(1899 views)
| Posted in reply to message from MarkDayton 03-31-2020

Something like this might work.....but it will be slow if you have a lot of data

```
dt = Current Data Table();
curDate = :Date;
curState = :State;
Try( :cases - :cases[(dt < get rows where( :date == (curDate - In Days( 1 )) & :State == curState ))[1]] );
```

Jim

3 REPLIES 3

Highlighted

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

Created:
Mar 31, 2020 1:51 PM
| Last Modified: Mar 31, 2020 2:02 PM
(1900 views)
| Posted in reply to message from MarkDayton 03-31-2020

Something like this might work.....but it will be slow if you have a lot of data

```
dt = Current Data Table();
curDate = :Date;
curState = :State;
Try( :cases - :cases[(dt < get rows where( :date == (curDate - In Days( 1 )) & :State == curState ))[1]] );
```

Jim

Highlighted
##

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

Re: Calculate Rate of Change Grouped by Column

Created:
Mar 31, 2020 2:36 PM
| Last Modified: Mar 31, 2020 2:38 PM
(1887 views)
| Posted in reply to message from txnelson 03-31-2020

With one correction, change from: "dt < get rows", to: "dt << get rows"

Highlighted
##

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

Re: Calculate Rate of Change Grouped by Column

oops....my error......good catch

Jim

Article Labels

There are no labels assigned to this post.