cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
joann
Level IV

combine actions/scripts

Hi,

 

I'd like to do a tabulate, make into data table, and then add calculated metric based on the data table. Below is what I have now (copying script of each action). I'm not sure how to combine the action of data table and the new metric. Please help, thanks in advance!

 

dt = Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Analysis Columns(
				:Name( "201733 Weekly COMP Store Sales" ),
				:Name( "201833 Weekly COMP Store Sales" )
			)
		),
		Row Table(
			Grouping Columns( :Name( "18 IRI Market" ) ),
			Add Aggregate Statistics( :Name( "18 IRI Market" ) )
		)
	)
);
 
dt << makeintodatatable;
 
 
dt << New Column( "ratio",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( :Name( "Sum(201833 Weekly COMP Store Sales)" ) / :Name( "Sum(201733 Weekly COMP Store Sales)" ) ),
	Set Selected
);
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: combine actions/scripts

See my comments in my changes to your script below

dt = current data table();

// dt normally is a reference to a data table, as I have referenced
// in the statement above.  The variable "tab", below, will refer to the
// tabulate platform 
tab = dt << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Analysis Columns(
				:Name( "201733 Weekly COMP Store Sales" ),
				:Name( "201833 Weekly COMP Store Sales" )
			)
		),
		Row Table(
			Grouping Columns( :Name( "18 IRI Market" ) ),
			Add Aggregate Statistics( :Name( "18 IRI Market" ) )
		)
	)
);

// the dt2 variable will point to the new output data table
dt2 = tab << makeintodatatable; 
 
// I am a little confused as to what data table you really want to 
// add the new column to......I am changing the dt data tablereference 
// to dt2, it is in the output data table that thes columns exist
dt2 << New Column( "ratio",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		:Name( "Sum(201833 Weekly COMP Store Sales)" ) /
		:Name( "Sum(201733 Weekly COMP Store Sales)" )
	),
	Set Selected
);
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: combine actions/scripts

See my comments in my changes to your script below

dt = current data table();

// dt normally is a reference to a data table, as I have referenced
// in the statement above.  The variable "tab", below, will refer to the
// tabulate platform 
tab = dt << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Analysis Columns(
				:Name( "201733 Weekly COMP Store Sales" ),
				:Name( "201833 Weekly COMP Store Sales" )
			)
		),
		Row Table(
			Grouping Columns( :Name( "18 IRI Market" ) ),
			Add Aggregate Statistics( :Name( "18 IRI Market" ) )
		)
	)
);

// the dt2 variable will point to the new output data table
dt2 = tab << makeintodatatable; 
 
// I am a little confused as to what data table you really want to 
// add the new column to......I am changing the dt data tablereference 
// to dt2, it is in the output data table that thes columns exist
dt2 << New Column( "ratio",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		:Name( "Sum(201833 Weekly COMP Store Sales)" ) /
		:Name( "Sum(201733 Weekly COMP Store Sales)" )
	),
	Set Selected
);
Jim
joann
Level IV

Re: combine actions/scripts

Thank you so much Jim!
Yes this is exactly what I need. Adding calculated metric on top of the new data table generated by tabulate. Thanks a lot!
joann
Level IV

Re: combine actions/scripts

Quick question: is there a Groupby function in JSL (like in python) to replace the tabulate function?
txnelson
Super User

Re: combine actions/scripts

Try Summarize().  Also, you can add an "Invisible" parameter to the Tabulate Platform, so you never see the actual display....

Jim