13995_en-US

liliconcom

How to compare each row to the one before<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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

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

);

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.

Re: How to compare each row to the one before

Thank you!

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

);