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
- :
- Discussions
- :
- How to compare each row to the one before

Topic Options

- 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
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 25, 2015 2:35 AM
(3336 views)

Dear JMP users/developers,

I have a data set and I would like to compare the increase/decrease in percentages for each column by year

Let's say I would like to know the percentage of RoomNight change compared to the year before.

Could any one please tell me the formula for it?

Many thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions

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

Aug 25, 2015 3:18 AM
(5460 views)

You can use the Lag() function. For example:

New Column**(**"Annual Change",

Numeric,

Format**(**"Percent", **12**, **1****)**,

Formula**(**If**(**Row**()** != **1**, **((**:Room Night - Lag**(**:Room Night, **1****))** / Lag**(**:Room Night, **1****))))**

**)**;

4 REPLIES

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

Aug 25, 2015 3:18 AM
(5461 views)

You can use the Lag() function. For example:

New Column**(**"Annual Change",

Numeric,

Format**(**"Percent", **12**, **1****)**,

Formula**(**If**(**Row**()** != **1**, **((**:Room Night - Lag**(**:Room Night, **1****))** / Lag**(**:Room Night, **1****))))**

**)**;

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

Aug 25, 2015 3:26 AM
(3069 views)

Thank you!

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

Aug 26, 2015 3:40 AM
(3069 views)

Thank you again and what is the logic of Lag() when comparing all years to the first year or second (or any selected year)?

Lets say I would like to compare the Room Night inc/dec to the 2007 or 2013?

Many many thanks for your support.

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

Aug 26, 2015 5:16 AM
(3069 views)

Lag() always refer to a row relative to the current row. To compare with a fixed reference year it may be easier to use an index. Lag() can also be used but would require that the data is regular with no missing years.

// Using a fixed index, assuming 2007 in first row

New Column**(**"Change since 2007",

Numeric,

Format**(**"Percent", **12**, **1****)**,

Formula**((**:Room Night - :Room Night**[****1****])** / :Room Night**[****1****])**

**)**;

// The same using lag (assuming there are no missing years)

New Column**(**"Change since 2007_2",

Numeric,

Format**(**"Percent", **12**, **1****)**,

Formula**((**:Room Night - Lag**(**:Room Night, :Year**[**Row**()]** - **2007****))** / Lag**(**:Room Night, :Year**[**Row**()]** - **2007****))**

**)**;