BookmarkSubscribe
Choose Language Hide Translation Bar
DarkHorse
New Contributor

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

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:

Capture.PNG

 

What I want:

  Capture2.PNG

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
stan_koprowski
Community Manager Community Manager

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

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. Screenshot 2019-01-17 23.47.13.pngbar element property changes

     Screenshot 2019-01-17 23.33.03.pngy-axis setting best format

    Screenshot 2019-01-17 23.47.24.pngCount 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

7 REPLIES 7
Highlighted
Thierry_S
Community Trekker

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

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

SUMMARIZE PICT.PNG

Thierry R. Sornasse
DarkHorse
New Contributor

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

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!

 

 

0 Kudos

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

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.  

CaptureGB.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

HTH

DarkHorse
New Contributor

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

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.

 

Capture2.PNG

0 Kudos

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

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.  

 

 

stan_koprowski
Community Manager Community Manager

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

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. Screenshot 2019-01-17 23.47.13.pngbar element property changes

     Screenshot 2019-01-17 23.33.03.pngy-axis setting best format

    Screenshot 2019-01-17 23.47.24.pngCount 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

DarkHorse
New Contributor

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

Hi @stan_koprowski,

 

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

 

 

0 Kudos