BookmarkSubscribeRSS Feed
MariaK

Community Trekker

Joined:

Jul 3, 2017

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
Highlighted
stan_koprowski

Community Manager

Joined:

Aug 8, 2012

Solution

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

 

Screen Shot 2018-05-02 at 5.19.33 PM.pngMonthly Averagescheers,

Stan 

 

 

 

4 REPLIES
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Jul 3, 2017

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
Highlighted
stan_koprowski

Community Manager

Joined:

Aug 8, 2012

Solution

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

 

Screen Shot 2018-05-02 at 5.19.33 PM.pngMonthly Averagescheers,

Stan 

 

 

 

MariaK

Community Trekker

Joined:

Jul 3, 2017

Re: Tabulate - Add Monthly Average?

Thank you Stan!  Worked like a charm. :)