BookmarkSubscribeRSS Feed
Highlighted
saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

How to compare each row to the one before

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

Re: How to compare each row to the one before

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

Re: How to compare each row to the one before

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

Re: How to compare each row to the one before

Thank you!

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Re: How to compare each row to the one before

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

Re: How to compare each row to the one before

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

);