nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Tabulate - Add Monthly Average?

MariaK

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:

Accepted answers

stan_koprowski

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 Averagescheers,

Stan

All comments

Jeff_Perkinson

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.

MariaK

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.

Series	Sum(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
Apples	20.29	21.45	21.48	21.53	22.02	22.75	23.47	23.14	22.76	20.50	19.95	19.95	21.61
Bananas	11.27	11.92	12.58	12.64	12.52	12.47	12.34	11.49	11.27	11.09	11.04	11.04	11.81
Bread	20.49	20.49	20.54	20.65	20.67	20.70	20.81	19.79	19.78	19.77	19.93	19.90	20.29
Chicken	24.59	24.62	24.76	24.68	24.48	24.79	25.15	24.14	24.28	23.99	24.01	23.83	24.44
Coffee	81.68	82.30	82.35	82.85	82.93	82.78	83.69	81.28	80.31	79.48	78.12	77.67	81.29
Eggs	12.84	12.66	12.33	12.32	11.39	11.40	11.37	12.05	11.96	11.90	12.31	13.20	12.14
Electricity	534.68	534.80	536.46	536.72	539.12	551.89	555.66	495.68	495.26	487.80	484.41	483.88	519.70
Fuel Oil #2	14.95	15.68	15.55	15.35	15.11	14.86	14.82	12.38	13.11	13.52	13.56	13.59	14.37
Gasoline, All	16.23	16.53	17.23	18.31	18.80	18.70	18.62	15.76	16.48	16.15	15.47	14.87	16.93
Gasoline, Unleaded	15.61	15.94	16.64	17.72	18.18	18.08	18.01	15.19	15.91	15.57	14.91	14.30	16.34
Ground Chuck	52.28	52.43	52.41	52.25	52.17	52.11	51.62	49.54	49.55	49.77	50.07	50.35	51.21
Lettuce	19.04	16.30	17.55	18.43	18.13	16.56	16.11	16.02	17.22	17.14	17.95	18.56	17.42
Milk	22.93	22.90	22.83	22.78	22.83	22.90	22.87	22.93	23.01	23.20	23.36	23.45	23.00
Natural Gas	445.93	436.58	432.23	429.50	432.26	431.47	433.34	377.69	384.47	393.11	404.12	405.20	417.16
Orange Juice	45.48	46.15	45.92	46.53	46.22	46.48	46.57	44.62	44.24	44.31	44.05	43.80	45.36
Oranges	15.19	15.31	15.77	16.36	15.65	6.60	1.27		2.51	3.69	16.45	15.46	11.30
Tomatoes	32.06	31.61	32.56	32.50	30.48	29.48	27.92	24.75	24.66	26.76	29.51	31.86	29.51

stan_koprowski

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 Averagescheers,

Stan

MariaK

Thank you Stan! Worked like a charm.