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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 25, 2015 2:35 AM
(2601 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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Solution

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
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 25, 2015 3:18 AM
(4669 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
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 26, 2015 3:40 AM
(2334 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
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 26, 2015 5:16 AM
(2334 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****))**

**)**;