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

- JMP User Community
- :
- File Exchange
- :
- JMP Add-Ins
- :
- "Percent of Total" Formula Column Utility

- Article History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content

Created:
Jan 12, 2017 8:01 AM
| Last Modified: Jan 12, 2017 8:48 AM

This Add-in generates formula columns calculating the percent of total (row-wise) for the selected numeric columns. These columns are especially useful when creating stacked bar or column chart of the columns of interest and you wish for your scale to be percent of total rather than a sum.

Usage: After installing this Add-in, select the set of columns in the data table, then choose "Percent of Total Formula Columns" from the Add-ins menu.

Note: The formulas generated by this Add-in will tolerate missing values row-wise for all but one of the selected columns.

When creating a stacked bar or column chart, the resulting plot is of the composition, rather than the total:

Comments

- Mark as Read
- Mark as New
- Bookmark
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-10-2017
07:57 PM

Very helpful!

- Mark as Read
- Mark as New
- Bookmark
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2017
11:11 PM

Hi Julian, Many thanks for this add-in. It's enormously helpful for analysis of compositional data. Much appreciated!

However, to analyze compositional data in a multivariate manner we need to convert row-wise "counts" to Aitchison's 'centered log ratio' (CLR) for all the columns that are components of the composition (e.g. all the columns that are included in the "percent of total" calculation).

It's very laborious to do this manually in JMP (although we do it for small compositions), and for work with gene transcript or microbiome sequencing data it is not even feasible.

The equation is simple, its just the natural log of the ratio of a given component over the *geometric* mean of all the components of the composition. Thus, for a 3 part composition (A, B, C) A is transformed as: LN(A/((A*B*C)^0.33)); B as LN(B/((A*B*C)^0.33)) and so on for C.

Unfortunately I am not fluent in the scripting (yet)... Would you be able to create a version of the 'Percent of Total' Add In that could instead produce columns of CLR transformed values for the composition?

Particularly, with subcompositional coherence, we are constantly having to make several new different subcompositions on an ongoing basis. If we have a 100 component microbe composition, we might produce 10 or so different subcompositions each having a different set of columns included in the subcomposition... It would be extraordinarily helpful to multi-select columns and produce the CLR transformations.

Thanks much!

- Mark as Read
- Mark as New
- Bookmark
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2017
11:39 PM

Hi @marxx,

I'm not familiar with the centered log ratio but from your description the calculation seems straightforward enough. I put together a version of this add-in that calculates the CLR and posted it here:

Download CLR Formula Columns Add-in

Would you check to see if this add-in produces the columns you expect? If this is functioning properly I'll post this as a separate add-in in the user community for others to use.

I hope this helps!

- Mark as Read
- Mark as New
- Bookmark
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-15-2017
10:19 AM

Hi Julian,

I tested the JMP CLR Add In versus a 6 component composition that I had

created manually... And the add-in you produced matched all the columns

that had been manually produced. I really appreciate it!

Thanks so much!

Marxx

[Edit: Looks like screenshot image can't be included, but it works].

[image: Inline image 1]

- Mark as Read
- Mark as New
- Bookmark
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-14-2019
06:32 PM

Hi Julian,

Would you please repost your CLR Formula Columns Add-in? I was assigned a new computer at work and lost all my previous addins.

PS- I couldn't find it posted in it's own post... just the link here that you posted previously.

Thanks, marxx

- Mark as Read
- Mark as New
- Bookmark
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-16-2019
10:08 AM

Hi @marxx,

Sorry to hear you lost all your Add-ins! Yes, I just linked to it here since it's really just a variant of this. Here's an updated link: Download CLR Formula Columns Add-In

Let me know if you have any trouble downloading.

julian

- Article History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content