topic Re: Can JMP Tabulate provide relative changes of parametrical data relative a reference point in time (such as pivot tables)? in Discussions

Can JMP Tabulate provide relative changes of parametrical data relative a reference point in time (such as pivot tables)?

JCarlsson — Sun, 11 Jun 2023 00:01:27 GMT

I have a lot of data sets that i normally review with Excel Pivot Tables, but wonder if it would be possible to use tabulate tables in JMP to review similarily.

The data looks like the example in the attached Excel table. Four devices are tested 5 times, at 0h, 1h, 20h, 50h, 100h.

In each measurement parameter1 and parameter2 are measured. I would like a table that has data in tree columns

for each device/time: Data value, Diff in data value relative first measurement, Diff in data value in percent relative first measurement.

Today I import very large data sets into JMP (10-100 parameters, thousands of devices),I export data to Excel

for creating pivot tables that reorganize data with pivot tables to add the columns that show parametrical changes

and thereafter I import data back to JMP for plotting, degradation analysis, correlation plots etc

I can of course do this in JMP with scripts that splits the table, adds new column with formulas, stacks these, renames new columns, etc, but that is a very inflexible way to do it and I haven´t found a way to do this dynamically without hard coding the test times.

The excel pivot tables are unfortunately much easier to work with.. but I don´t like the fact that I need to jump forth and back to Excel

with all the data.

Any ideas if this can be done in JMP with the tabulate tables?

Re: Can JMP Tabulate provide relative changes of parametrical data relative a reference point in time (such as pivot tables)?

jthi — Wed, 24 May 2023 07:24:03 GMT

I think you will have to add couple of formulas and then you can create tabulate/summary table like this

I would create a column to calculate the Zero value. Depending on your data something simpler (or more complicated) could be used

Col Mean(
	If(Col Min(:Time, :Device, :Group, :Batch, :Name) == :Time,
		:Value,
		.
	),
	:Device, :Group, :Batch, :Name
);

Then create the calculation columns

:Value / :GroupZero - 1

and

:Value - :GroupZero

Below is the script JMP created to me (I would clean it up a lot and make it more robust)

// Delete column: Column 7
Data Table("Data") << Delete Columns(:Column 7);

// New column: Column 7
Data Table("Data") << New Column("Column 7",
	Numeric,
	"Continuous",
	Format("Best", 12)
);

// Change column info: GroupZero
Data Table("Data"):Column 7 << Set Name("GroupZero") <<
Set Formula(
	Col Mean(
		If(Col Min(:Time, :Device, :Group, :Batch, :Name) == :Time,
			:Value,
			.
		),
		:Device,
		:Group,
		:Batch,
		:Name
	)
);

// New column: Column 8
Data Table("Data") << New Column("Column 8",
	Numeric,
	"Continuous",
	Format("Best", 12)
);

// Change column info: Diff to 0
Data Table("Data"):Column 8 << Set Name("Diff to 0") <<
Set Formula(:Value - :GroupZero);

// New column: Column 9
Data Table("Data") << New Column("Column 9",
	Numeric,
	"Continuous",
	Format("Best", 12)
);

// Change column info: Diff to 0perc
Data Table("Data"):Column 9 << Set Name("Diff to 0perc") << Format("Percent", 12, 2)
 << Set Formula(:Value / :GroupZero - 1);

// Change column modeling type: Device
Data Table("Data"):Device << Set Modeling Type("Nominal");

// Change column modeling type: Batch
Data Table("Data"):Batch << Set Modeling Type("Nominal");

// Change column modeling type: Time
Data Table("Data"):Time << Set Modeling Type("Nominal");

// Report snapshot: Data - Tabulate
Tabulate(
	Show Control Panel(0),
	Add Table(
		Column Table(Analysis Columns(:Value), Statistics(Mean)),
		Column Table(Analysis Columns(:Diff to 0, :Diff to 0perc), Statistics(Mean)),
		Row Table(Grouping Columns(:Device, :Group, :Batch, :Name, :Time))
	)
);

Re: Can JMP Tabulate provide relative changes of parametrical data relative a reference point in time (such as pivot tables)?

JCarlsson — Wed, 24 May 2023 11:15:04 GMT

Wow! Thank you Jarmo!

I´d never guess that Col Mean could be used in that way...

If´ve obviously missed some basics.

Many many thanks, I´ve got some serious cleaning to to in my scripts :)

/Jessica