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

Showing results for

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

- 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
(1487 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

Solution

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
(1307 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
(1307 views)

- 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
(1307 views)

- 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
(1307 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
(1307 views)

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

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
(1307 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...