day difference in the same column of different scenarios

joann

Hi, I have questions for different scenarios as below. Really appreciate any help!

Scenario A: I want to add this last column, calculating for that userID, what's the day difference from first web to first booking date. If he had no web action prior to booking action, then leave it empty. Could someone help me with what the formula should look like using the function but not jsl?

userID	action	date	first web date to first booking date
2523	web	2018-05-06	22
2523	web	2018-05-16	22
2523	booking	2018-05-28	22
2523	booking	2018-06-08	22
2523	web	2018-07-08	22
2523	booking	2018-08-08	22

Scenario B: based on the above table, I want to calculate another table like the below that contains for each userID, day difference between 'the first web action date' 'before bookings' to each booking date. Based on the logic, the ideal result with the above table as input will be as below, 3 instances of day difference for this userID. Same as in scenario A, we only consider when web is prior to booking.

userID	instance	calculation
2523	22	5/28 minus 5/6
2523	33	6/8 minus 5/6
2523	31	8/8 minus 7/8

Thank you!

Georg

Hi @joann , sorry to say but this is not the platform for full service, but we are happy if we can support you. So if you want to learn please see the online documentation on the functions used here.

E.g. loc gives you the index of a vector for certain values:

Loc Functions (jmp.com)

Please see below script for both scenarios. Scenario B is quite similar ...

If you want to have the exact table for scenario B as you described, you can delete the formula (that keeps the values then), and select the missing values in column "Formula B" and delete them.

BR 

 

names default to here(1);

// create the example table
New Table( "20210404_user_web_booking_matrixexample",
	Add Rows( 12 ),
	Compress File When Saved( 1 ),
	New Column( "userID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[2523, 2523, 2523, 2523, 2523, 2523, 2540, 2540, 2540, 2540, 2540, 2540]
		),
		Set Display Width( 66 )
	),
	New Column( "action",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"web", "web", "booking", "booking", "web", "booking", "web", "web",
			"booking", "booking", "web", "booking"}
		),
		Set Display Width( 73 )
	),
	New Column( "date",
		Numeric,
		"Continuous",
		Format( "y/m/d", 12 ),
		Input Format( "y/m/d" ),
		Set Values(
			[3608409600, 3609273600, 3610310400, 3611260800, 3613852800, 3616531200,
			3647462400, 3647548800, 3647635200, 3647721600, 3647808000, 3647894400]
		),
		Set Display Width( 92 )
	),
	New Column( "first web date to first booking date",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [22, 22, 22, 22, 22, 22, ., ., ., ., ., .] ),
		Set Display Width( 150 )
	),
	New Column( "formula_A",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Names Default To Here( 1 );
			// convert columns to vectors
			dat_vec = :date << get values;
			web_vec = :action << get values;
			user_vec = :userID << get values;
			// get a vector with subscripts of current userID
			user_index = user_vec == :userID[Row()];
			// get a vctor with subscripts where action=web
			web_index = Matrix(
				Substitute( :action << get values, "web", 1, "booking", 0 )
			);
			// get a vector with dates for current userID and action=web
			web_dat = dat_vec[Loc( user_index & web_index )];
			// the same for booking (actually not web)
			booking_dat = dat_vec[Loc( user_index & !web_index )];
			// calculate minimum of both date vectors and subtract
			(Minimum( booking_dat ) - Minimum( web_dat )) / (3600 * 24);
		),
		Set Display Width( 146 )
	),
	New Column( "formula_B",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Names Default To Here( 1 );
			dat_vec = :date << get values;
			web_vec = :action << get values;
			user_vec = :userID << get values;
			user_index = user_vec == :userID[Row()];
			web_index = Matrix(
				Substitute( :action << get values, "web", 1, "booking", 0 )
			);
			web_dat = dat_vec[Loc( user_index & web_index )];
			booking_dat = dat_vec[Loc( user_index & !web_index )];
			// when current action is booking, then subtract minimum web date, otherwise keep empty, and divide seconds by seconds of day
			If( :action == "booking", :date - Minimum( web_dat ), . ) / (3600 * 24);
		),
		Set Selected
	)
);