77316_en-US

liliconcom

create calculated metric in tabulate and automate it

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

Re: create calculated metric in tabulate and automate itThanks a lot Jim!
Could you let me know how to do that with user interface in tabulate? Do I need to add a new column first in data table with the formula ( Col Sum( :Sales, :city ) / Col Sum( :cost, :city ) ?Re: create calculated metric in tabulate and automate it

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