create calculated metric in tabulate and automate it

joann

Hi,

 

I wonder how to add a calculated field in jmp tabulate, similar to add calculated field in excel pivot table?

For example the data is like below:

 

Date sales cost city

1/1   $100  $30 SF

1/1   $50    $20 SF

1/2   $800  $100 NY

1/1   $50    $20 NY

1/3   $100  $40 SF

1/3   $80    $70 SF

 

 

And I want to do Sales/cost by City. How do I add Sales/cost metric after putting city into tabulate? I don’t know JSL but since this will be an ongoing reports, I’m trying to copy the scrip behind it and automate the process.

 

Thanks,

Joann

txnelson

Here is one solution, using a Transform column from the selection window

Tabulate(
	Add Table(
		Column Table( Analysis Columns( :Sales ) ),
		Column Table( Analysis Columns( :cost ) ),
		Column Table(
			Analysis Columns(
				Transform Column(
					"Transform[cost]",
					Formula( Col Sum( :Sales, :city ) / Col Sum( :cost, :city ) )
				)
			),
			Statistics( Mean )
		),
		Row Table( Grouping Columns( :city ) )
	)
);