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
danlopez
Level I

How to create a calculated column similar to pivot table in Excel?

I want to use graph builder to slice and dice attrition data. In Excel I would use what is called a calculated field within a pivot table.

My data would look like the attached, where I can select any field as row(s)s or column(s) and the attrition rate would update dynamically since its a caculated field.

How do I do this in JMP? I tried HC/TER and sum(HC)/sum(TER). I did this in the data detail as as a separate column. Neither worked since they only caculate once at the individual level. I need something that will

perform the caculation on update.

1 ACCEPTED SOLUTION

Accepted Solutions
XanGregg
Staff

Re: How to create a calculated column similar to pivot table in Excel?

I don't think JMP can do exactly that, with an arbitrary formula evaluated over the dynamic grouping.

If you know the grouping and can create a formula, be sure to use the Col functions and provide the grouping column(s) as BY variable(s):   Col Sum( :HC, :KeyEE ) / Col Sum( :TER, :KeyEE )

Otherwise you can compare sums (inferring the ratio) with a table in Tabulate:

4055_hier1.png

Or graphically in Graph Builder:

4056_hier2.png

View solution in original post

1 REPLY 1
XanGregg
Staff

Re: How to create a calculated column similar to pivot table in Excel?

I don't think JMP can do exactly that, with an arbitrary formula evaluated over the dynamic grouping.

If you know the grouping and can create a formula, be sure to use the Col functions and provide the grouping column(s) as BY variable(s):   Col Sum( :HC, :KeyEE ) / Col Sum( :TER, :KeyEE )

Otherwise you can compare sums (inferring the ratio) with a table in Tabulate:

4055_hier1.png

Or graphically in Graph Builder:

4056_hier2.png