turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Delaying (lag) a row value by column date

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 24, 2016 2:10 PM
(4712 views)

Dear All,

I would like to make a simple calculation but I can not.

Attached jmp table is a subset of Hotel Occupancy Data Base.

Simple logic for Record ID 27421 is;

in 14 Apr 2016 250 Rooms arrive and check in,

in 14-15-16 Apr 2016 those 250 Rooms stay at the hotel for 3 days long,

in 17.04.2015 the 250 Rooms which arrived in 14 Apr 2016 Check Out (Leave the hotel).

I need to calculate the RoomLeave Column (which I filled manually for the purpose)..

Record ID is unique but many more arrivals/stay/leave occur at the same dates with different Record IDs.

Many thanks in advance for any of your support.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 25, 2016 5:18 AM
(8236 views)

Are the data for each RecordID always grouped together and in ascending order based upon StayDate?

If they are, here is a formula that will work:

If( :RecordId != Lag( :RecordId ) | Row() == 1,

ar = :ArrivalRoom

);

If( Date Difference( :StayDate, :CheckOutDate, "Day" ) == 1,

ar,

0

);

Jim

7 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 24, 2016 3:48 PM
(4532 views)

Would the date difference function work for you?

If Date Difference(CheckOutDate, CheckInDate, Day) = 1 then room leave = room night, else 0.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 25, 2016 9:04 AM
(4532 views)

Dear karen@boulderstats thank you for the input. Either I did it wrong or the formula itself gives error.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 25, 2016 9:55 AM
(4532 views)

Yes, I didn't provide the exact correct formula. You got close. It is the "Day" piece that is tricky. Jim used the same formula in the script he wrote only he has it correct in that you need the quotes around "Day". With many JMP formula's the options can be tricky (they generally make sense once you see them) so I find I often need the user manual to get all of the pieces correct.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 24, 2016 7:13 PM
(4532 views)

Here is a quick script that performs the data table updating you want.

Names default to here(1);

dt=current data table();

// Create the RoomLeave column and initialize it

If(Try(dt:RoomLeave<<get name,"")!="", dt<<delete columns("RoomLeave"));

wait(.2);

dt<< New Column("RoomLeave", set each value(0));

// Find the last staydate and the Arrival Room for each RecordID

dtsumm = dt << Summary(invisible,

Group( :RecordId ),

Max( :StayDate ),

Max( :ArrivalRoom ),

Freq( "None" ),

Weight( "None" ),

statistics column name format( "column" )

);

// Change the ArrivalRoom to the RoomLeave

dtsumm:ArrivalRoom << Set Name("RoomLeave");

// Cleanup the data table to keep only the columns needed

dtsumm << delete columns("N Rows");

// Update the original data table with the Leaving data

// Matching on the RecordID and the last staydate

dt << Update(

With(dtsumm ),

Match Columns( :RecordId = :RecordId, :StayDate = :StayDate )

);

// Delete the summary data table

close( dtsumm, nosave );

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 25, 2016 2:01 AM
(4532 views)

Dear Jim Nelson,

Thank you for the input it does the job but I need to solve it with a formula column, the data base is huge and updating itself in every minute.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 25, 2016 5:18 AM
(8237 views)

Are the data for each RecordID always grouped together and in ascending order based upon StayDate?

If they are, here is a formula that will work:

If( :RecordId != Lag( :RecordId ) | Row() == 1,

ar = :ArrivalRoom

);

If( Date Difference( :StayDate, :CheckOutDate, "Day" ) == 1,

ar,

0

);

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 25, 2016 9:13 AM
(4532 views)

Thank you Jim Nelson! It works and yes the data is in ascending order.

Many thanks again, I can pull my report.

P.S.: Unfortunately the **'day'** have to come from the **StayDate** for the **ArrivalRoom** and **RoomNight** but does not fit for **RoomCout** which needs a +1 day delay (A guest can not stay and Check Out same day). Acc. to my logic it is impossible to solve it in one tabulate report like the one above...