topic Re: Barchart to show count of unique values in a column by another categorical column - JSL in Discussions

Barchart to show count of unique values in a column by another categorical column - JSL

DarkHorse — Wed, 16 Jan 2019 22:38:02 GMT

Hi all,

I am a newbie here. I was trying to build a barchart where the X- axis (bar categories) is a categorical column in my table and Y-axis (bar height) is the count of unique values in another column. Code for troubleshooting:

// Make data table
t = New Table( "test", Add Rows( 5 ),
New Column( "Customer", Character, "Nominal",
Set Values( {"A", "A", "B", "B", "B", "C", "D", "E","E"} ) ),
New Column( "OrderID", Character, "Nominal",
Set Values( {"1", "2", "3", "4", "5", "6", "7", "8","9"} ) ),
New Column( "Prime member", Character,"Nominal",
Set Values( {"Non-Prime", "Non-Prime", "Prime", "Prime", "Prime", "Non-Prime", "Non-Prime", "Prime","Prime"} ) ),
);

Please see the screenshots for what I'm looking for. My data table:

What I want:

Re: Barchart to show count of unique values in a column by another categorical column - JSL

Thierry_S — Wed, 16 Jan 2019 21:20:11 GMT

Hi,

I cannot think of a method to produce the desired bar chart directly from your data table but, the Summary feature will produce a new data table with the correct numbers: Statistics = N Categories (CUSTOMER) and GROUP = Membership

Re: Barchart to show count of unique values in a column by another categorical column - JSL

DarkHorse — Thu, 17 Jan 2019 17:13:59 GMT

Hi @Thierry_S,

Thanks for the reply! This is helpful.. But not exactly what I wanted.

I want to make this chart and attach to the main table itself. Because I will have other charts as well which I want to put together in a dashboard. Thanks agian!

Re: Barchart to show count of unique values in a column by another categorical column - JSL

Bill_Worley — Thu, 17 Jan 2019 18:49:13 GMT

Hello Darkhorse,

Please check out the graph below. This was built in Graph Builder with a couple of drags and a click or something like that. Since you are newer to JMP I am guessing you have not used Graph Builder.

HTH

Re: Barchart to show count of unique values in a column by another categorical column - JSL

DarkHorse — Thu, 17 Jan 2019 20:52:15 GMT

Hi @Bill_Worley,

Thanks for your reply!

I'm looking to report the unique count of customers by prime membership. So, the count on PRIME would be 2 and NON PRIME would be 3 as in the below chart. I guess there should be some grouping by customer and summarize before doing this chart.

Re: Barchart to show count of unique values in a column by another categorical column - JSL

Bill_Worley — Fri, 18 Jan 2019 02:43:30 GMT

You hit it with grouping and summarizing. Use Tables > Summary and place Prime Member and Customer into Group. This will give you a new table and you can make your chart from there.

Re: Barchart to show count of unique values in a column by another categorical column - JSL

stan_koprowski — Fri, 18 Jan 2019 04:53:10 GMT

Hi @DarkHorse,

I think you might be able to do what you want as follows--

Drag Prime member column to x-axis.
Drag Customer column to overlay role.
Drag Prime member column to color role.
Select the bar element.
Change the bar element property to stacked.
Change the bar element summary statistic to % of total.
Double click the y-axis and chane the format to best from percent.

bar element property changes
y-axis setting best format
Count of Customer by prime membership

And is the JSL script to reproduce.

Graph Builder(
	Size( 535, 447 ),
	Show Control Panel( 0 ),
	Variables( X( :Prime member ), Overlay( :Customer ), Color( :Prime member ) ),
	Elements(
		Bar(
			X,
			Legend( 3 ),
			Bar Style( "Stacked" ),
			Summary Statistic( "% of Total" )
		)
	),
	SendToReport(
		Dispatch( {}, "", ScaleBox, {Format( "Best", 9 )} ),
		Dispatch(
			{},
			"400",
			ScaleBox,
			{Legend Model(
				3,
				Properties( 0, {Fill Color( 5 )}, Item ID( "Non-Prime", 1 ) ),
				Properties( 1, {Fill Color( 19 )}, Item ID( "Prime", 1 ) ),
				Properties( -1, {Fill Color( 32 )}, Item ID( "A", 1 ) ),
				Properties( -1, {Fill Color( 32 )}, Item ID( "B", 1 ) ),
				Properties( -1, {Fill Color( 32 )}, Item ID( "C", 1 ) ),
				Properties( -1, {Fill Color( 32 )}, Item ID( "D", 1 ) ),
				Properties( -1, {Fill Color( 32 )}, Item ID( "E", 1 ) )
			)}
		)
	)
)

cheers,

Stan

Re: Barchart to show count of unique values in a column by another categorical column - JSL

DarkHorse — Fri, 18 Jan 2019 15:44:34 GMT

Hi @stan_koprowski,

Awesome! That worked perfect and exactly what I was looking for. Thanks a ton!

Re: Barchart to show count of unique values in a column by another categorical column - JSL

hogi — Wed, 12 Jun 2024 05:56:21 GMT

VERY (!) nice workaround :)

But:
In most of my cases, the Overlay role is already occupied and this workaround will not work.

~~Why is there no "N unique" in this list?~~
When will there be a "N unique" in this list?