## How to compare each row to the one before

Community Trekker

Joined:

Sep 29, 2014

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?

1 ACCEPTED SOLUTION

Accepted Solutions

Super User

Joined:

Jun 23, 2011

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

Super User

Joined:

Jun 23, 2011

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

);

Community Trekker

Joined:

Sep 29, 2014

Thank you!

Community Trekker

Joined:

Sep 29, 2014

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.

Super User

Joined:

Jun 23, 2011

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

);