Delaying (lag) a row value by column date

[#1]

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.

[#2]

Would the date difference function work for you?

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

[#3]

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

[#4]

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.

[#5]

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

[#6]

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.

[#7 · Accepted 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

);

[#8]

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