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

Tabulate - Add Monthly Average?

I'm trying to add the monthly average to my tabulate table...  what am I doing wrong?  It's not averaging the price by month...

For display purposes I'm using the sample data - consumer prices.

Script:

 

Tabulate(
Show Chart( 1 ),
Show Shading( 1 ),
Show Control Panel( 0 ),
Set Format( Uniform Format( 10, 2 ) ),
 
Add Table(
Column Table( Grouping Columns( :Month ), Analysis Columns( :Price ) ),
Column Table( Statistics( Mean ), Analysis Columns( :Price ) ),
Row Table( Grouping Columns( :Series ) )
)
);

 

Result:

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
stan_koprowski
Community Manager Community Manager

Re: Tabulate - Add Monthly Average?

Hi @MariaK,

One way to do what you want is to create a summary table and then run tabulate on the summary table.

 

dt = Open( "$SAMPLE_DATA\Consumer Prices.jmp" );

dt << Summary(
	Group( :Series, :Month ),
	Sum( :Price ),
	Freq( "None" ),
	Weight( "None" )
);

dt2 = Current Data Table();
dt2:Name("Sum(Price)")<<Set Name("Monthly Total");

dt2 << Tabulate(
	Change Item Label(
		Statistics( Mean, " " ),
		Grouping Columns( :Month( "All" ), "Monthly Average" )
	),
	Show Control Panel( 0 ),
	Set Format( Mean( :Monthly Total( 12, 2 ) ) ),
	Add Table(
		Column Table(
			Grouping Columns( :Month ),
			Add Aggregate Statistics( :Month ),
			Analysis Columns( :Monthly Total ),
			Statistics( Mean )
		),
		Row Table( Grouping Columns( :Series ) )
	)
);

 

Monthly AveragesMonthly Averagescheers,

Stan 

 

 

 

View solution in original post

4 REPLIES 4
Jeff_Perkinson
Community Manager Community Manager

Re: Tabulate - Add Monthly Average?

You're adding Mean as a Column Table in your script with the statement:

 

Column Table( Statistics( Mean ), Analysis Columns( :Price ) )

 

Instead you want to add it as an analysis column in the existing column table.

dt = Open( "$SAMPLE_DATA\Consumer Prices.jmp" );

dt << Tabulate(
	Show Chart( 1 ),
	Add Table(
		Column Table(
			Grouping Columns( :Month ),
			Analysis Columns( :Price ),
			Statistics( Sum, Mean )
		),
		Row Table( Grouping Columns( :Series ) )
	)
);

To get this syntax I built the table interactive and then asked JMP for the script from the red triangle hotspot.

JMPScreenSnapz195.png

-Jeff
MariaK
Level I

Re: Tabulate - Add Monthly Average?

Jeff,

 

I want to show the sum per month and the average for the year by month.

 

In excel I would take the average of all months. 

SeriesSum(Price, Jan)Sum(Price, Feb)Sum(Price, Mar)Sum(Price, Apr)Sum(Price, May)Sum(Price, Jun)Sum(Price, Jul)Sum(Price, Aug)Sum(Price, Sep)Sum(Price, Oct)Sum(Price, Nov)Sum(Price, Dec)Monthly Average
Apples20.2921.4521.4821.5322.0222.7523.4723.1422.7620.5019.9519.9521.61
Bananas11.2711.9212.5812.6412.5212.4712.3411.4911.2711.0911.0411.0411.81
Bread20.4920.4920.5420.6520.6720.7020.8119.7919.7819.7719.9319.9020.29
Chicken24.5924.6224.7624.6824.4824.7925.1524.1424.2823.9924.0123.8324.44
Coffee81.6882.3082.3582.8582.9382.7883.6981.2880.3179.4878.1277.6781.29
Eggs12.8412.6612.3312.3211.3911.4011.3712.0511.9611.9012.3113.2012.14
Electricity534.68534.80536.46536.72539.12551.89555.66495.68495.26487.80484.41483.88519.70
Fuel Oil #214.9515.6815.5515.3515.1114.8614.8212.3813.1113.5213.5613.5914.37
Gasoline, All16.2316.5317.2318.3118.8018.7018.6215.7616.4816.1515.4714.8716.93
Gasoline, Unleaded15.6115.9416.6417.7218.1818.0818.0115.1915.9115.5714.9114.3016.34
Ground Chuck52.2852.4352.4152.2552.1752.1151.6249.5449.5549.7750.0750.3551.21
Lettuce19.0416.3017.5518.4318.1316.5616.1116.0217.2217.1417.9518.5617.42
Milk22.9322.9022.8322.7822.8322.9022.8722.9323.0123.2023.3623.4523.00
Natural Gas445.93436.58432.23429.50432.26431.47433.34377.69384.47393.11404.12405.20417.16
Orange Juice45.4846.1545.9246.5346.2246.4846.5744.6244.2444.3144.0543.8045.36
Oranges15.1915.3115.7716.3615.656.601.27 2.513.6916.4515.4611.30
Tomatoes32.0631.6132.5632.5030.4829.4827.9224.7524.6626.7629.5131.8629.51
stan_koprowski
Community Manager Community Manager

Re: Tabulate - Add Monthly Average?

Hi @MariaK,

One way to do what you want is to create a summary table and then run tabulate on the summary table.

 

dt = Open( "$SAMPLE_DATA\Consumer Prices.jmp" );

dt << Summary(
	Group( :Series, :Month ),
	Sum( :Price ),
	Freq( "None" ),
	Weight( "None" )
);

dt2 = Current Data Table();
dt2:Name("Sum(Price)")<<Set Name("Monthly Total");

dt2 << Tabulate(
	Change Item Label(
		Statistics( Mean, " " ),
		Grouping Columns( :Month( "All" ), "Monthly Average" )
	),
	Show Control Panel( 0 ),
	Set Format( Mean( :Monthly Total( 12, 2 ) ) ),
	Add Table(
		Column Table(
			Grouping Columns( :Month ),
			Add Aggregate Statistics( :Month ),
			Analysis Columns( :Monthly Total ),
			Statistics( Mean )
		),
		Row Table( Grouping Columns( :Series ) )
	)
);

 

Monthly AveragesMonthly Averagescheers,

Stan 

 

 

 

MariaK
Level I

Re: Tabulate - Add Monthly Average?

Thank you Stan!  Worked like a charm. :)