Subscribe Bookmark RSS Feed

Stacked bar - show % contribution of each column (using Graph builder)

yanne

Community Trekker

Joined:

Sep 22, 2015

Dear all,

I am struggling in making a graph using the graph builder for something that is probably fairly easy…

 

I have the following fictional dataset, with 2 products A and B and for each of them a score in 3 categories/phases. This can easily be shown with a stacked bar.

1.png

 

 

FIG1:2.png

 

Now I want to see the results in % contributions to the total score each product has. I know I can do this by first summing the 3 columns Phase 1->3, and then divide the Phase1 score by this total etc, as I now manually did in the table. And then again, you can make a stacked bar to get the graph I need.

 

FIG2:4.png

So this definitely works. However, I now have a whole lot of columns to analyse, so manually dividing each column by the total seems like a big hurdle… Is there any fast way of converting FIG1 into FIG2??

I already tried with changing “summary statistic = % of total” in FIG1, but then my total is not 100% as shown in FIG3, so I think this gives me the % of total per column, while I want % of total per row (for those columns displayed in the chart).

5.png

Many thanks for your tips!

Yanne

1 ACCEPTED SOLUTION

Accepted Solutions
julian

Staff

Joined:

Jun 25, 2014

Solution

Hi @yanne,

 

As you have discovered, to make this plot in Graph Builder you will need to create the % of total formula columns. I have to do this sort of thing pretty often (calculate % of total columns for a set of columns in a data table) and to streamline the process I put together a simple Add-in to do it. I just uploaded that Add-in to the file exchange here:

 

https://community.jmp.com/t5/JMP-Add-Ins/quot-Percent-of-Total-quot-Formula-Column-Utility/ta-p/3441...

 

I hope this helps!

 

Julian

3 REPLIES
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Hello Yanne ,
You can probably try something like this , I proceed with a couple of assumptions here
Assumptions:
1. You capture the data table and its reference is "dt"

dt = Current Data Table(); 
ColNames = Get Column Names("String"); 
Mat = dt << Get As Matrix(); // Get the numeric data out of the data table 
ColSum = VSum(Transpose(Mat)); 
dt << New Column("Totals",Numeric,Continuous,Set Values(ColSum)); 
for( i = 1 , i <= N Items(ColNames), i ++, 
          If (i == 1, 
                  continue(); // assuming column 1 is always the product name 
              , 
                 Col = Column(i); 
                 Num = Col << Get Values(); 
                 Col = Column("Totals"); 
                 Den = Col << Get Values();
                 ValuesToSet = E Div(Num,Den); 
                 dt << New Column("%" || ColNames[i] ,Numeric,Continuous,Set Values(ValuesToSet ) ); 
            ); 
   ); 

This should solve the problem of getting % contributions for any number of variables. If you need the variable names for plotting , you can again re-run the "Get Column Names" and the plotting script you have in place will tackle the rest .


Best 
Uday 

Best
Uday
julian

Staff

Joined:

Jun 25, 2014

Solution

Hi @yanne,

 

As you have discovered, to make this plot in Graph Builder you will need to create the % of total formula columns. I have to do this sort of thing pretty often (calculate % of total columns for a set of columns in a data table) and to streamline the process I put together a simple Add-in to do it. I just uploaded that Add-in to the file exchange here:

 

https://community.jmp.com/t5/JMP-Add-Ins/quot-Percent-of-Total-quot-Formula-Column-Utility/ta-p/3441...

 

I hope this helps!

 

Julian

yanne

Community Trekker

Joined:

Sep 22, 2015

Dear Julian,

Many thanks for this add-in! this is exactly what I needed!!!

Many many thanks!!! You make my day as I too have to calculate this % pretty often, and for many different tables and columns, so this really makes my life so much easier!

Great add-in! thanks!!

Yanne