Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 12, 2017 4:29 AM
(9455 views)

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.

FIG1:

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:

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

Many thanks for your tips!

Yanne

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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:

I hope this helps!

Julian

3 REPLIES 3

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

Uday

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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:

I hope this helps!

Julian

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

Article Labels

There are no labels assigned to this post.