topic How to compare each row to the one before in Discussions

How to compare each row to the one before

saitcopuroglu — Wed, 19 Oct 2016 00:28:27 GMT

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

Re: How to compare each row to the one before

ms — Tue, 25 Aug 2015 10:18:33 GMT

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

);

Re: How to compare each row to the one before

saitcopuroglu — Tue, 25 Aug 2015 10:26:18 GMT

Thank you!

Re: How to compare each row to the one before

saitcopuroglu — Wed, 26 Aug 2015 10:40:16 GMT

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.

Re: How to compare each row to the one before

ms — Wed, 26 Aug 2015 12:16:50 GMT

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

);