Subscribe Bookmark RSS Feed

How to compare each row to the one before

saitcopuroglu

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?

Many thanks in advance

9649_2015-08-25_12-33-42.png

1 ACCEPTED SOLUTION

Accepted Solutions
ms

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
ms

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

);

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Thank you!

saitcopuroglu

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.

ms

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

);