topic Re: Tabulate - Add Monthly Average? in Discussions

Tabulate - Add Monthly Average?

MariaK — Wed, 25 Apr 2018 18:40:52 GMT

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:

Re: Tabulate - Add Monthly Average?

Jeff_Perkinson — Wed, 25 Apr 2018 18:52:04 GMT

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.

Re: Tabulate - Add Monthly Average?

MariaK — Wed, 25 Apr 2018 20:21:26 GMT

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

Re: Tabulate - Add Monthly Average?

stan_koprowski — Wed, 02 May 2018 21:22:18 GMT

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

Re: Tabulate - Add Monthly Average?

MariaK — Thu, 03 May 2018 15:31:40 GMT

Thank you Stan! Worked like a charm. :)