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

[#1]

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:

  

[#2]

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

[#3]

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!

[#4]

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

[#5]

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.

[#6]

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.  

[#7 · Accepted solution]

Hi @DarkHorse,

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

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

1. bar element property changes

    y-axis setting best format

   Count of Customer by prime membership

1. 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

[#8]

Hi @stan_koprowski,

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

[#9]

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?