turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Applying one formula to many sequencing type of columns

Topic Options

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

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

May 5, 2015 11:31 PM
(3155 views)

Hi all,

I come accross many times with this question;

Applying one type of formula to many newly generated columns. Could someone please explain the main logic behind it?

For example:

1. I have a data set of 72 years with 12 months. I would like to set one of the year/month to be the best and accept it as =100 and I would like to compare all others to that year/month in percentage (particularly revenue increase or decrease in percentage).

2. I have 1 column to think as MainColumn with 100 other columns (1,2,3,4,5,...) in a data table, and with adding 100 new columns I would like to use the same formula for each: NewColumn1 = Column1/MainColumn, NewColumn2=Column2/MainColumn...etc...

3. I have columns in paired relations (Column1 Column2, Column3 Column4) and would like to create new column (ColumnNew1) with a formula related to paired columns (=Column1+Column2), (ColumnNew2=Column3+Column4)...and so on.

How to do these?

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions

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

May 7, 2015 3:54 AM
(5224 views)
| Posted in reply to message from saitcopuroglu 05/06/2015 02:31 AM

I assume you looking for a scripting solution. Below are some ideas.

```
//Example tables
dt1 = New Table("example 1",
add rows(12 * 72),
New Column("year", numeric, Formula(Sequence(1944, 2015, 1, 12))),
New Column("month", numeric, values(Repeat(1 :: 12, 72))),
New Column("revenue", numeric, Formula(Random Integer(10000)))
);
dt2 = New Table("example 2", add rows(5), New Column("main column", numeric, values(1 :: 5)));
dt2 << add multiple columns("column", 10, after last, numeric);
For(i = 2, i <= 11, i++,
Column(dt2, i) << set formula(Random Integer(10))
);
// 1)
dt1 << New Column("revenue, % of year's best month", numeric, Format("percent"), Formula(:revenue / Col Max(:revenue, year)));
// 2) and 3) The trick is to use Eval Expr() and Name Expr() when defining formulas.
cols = dt2 << get column names;
// add formulas for 2
For(i = 2, i <= 11, i++,
colname = Char(cols[i]) || " by main";
f = Eval Expr(Expr(cols[i]) / :main column);
dt2 << New Column(colname, numeric, formula(Name Expr(f)));
);
// dito for 3
For(i = 2, i <= 6, i = i + 2,
colname = Char(cols[i]) || "+" || Char(cols[i + 1]);
f = Eval Expr(Expr(cols[i]) + Expr(cols[i + 1]));
dt2 << New Column(colname, numeric, formula(Name Expr(f)));
);
```

3 REPLIES

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

May 7, 2015 1:20 AM
(2965 views)
| Posted in reply to message from saitcopuroglu 05/06/2015 02:31 AM

Any ideas?

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

May 7, 2015 3:54 AM
(5225 views)
| Posted in reply to message from saitcopuroglu 05/06/2015 02:31 AM

I assume you looking for a scripting solution. Below are some ideas.

```
//Example tables
dt1 = New Table("example 1",
add rows(12 * 72),
New Column("year", numeric, Formula(Sequence(1944, 2015, 1, 12))),
New Column("month", numeric, values(Repeat(1 :: 12, 72))),
New Column("revenue", numeric, Formula(Random Integer(10000)))
);
dt2 = New Table("example 2", add rows(5), New Column("main column", numeric, values(1 :: 5)));
dt2 << add multiple columns("column", 10, after last, numeric);
For(i = 2, i <= 11, i++,
Column(dt2, i) << set formula(Random Integer(10))
);
// 1)
dt1 << New Column("revenue, % of year's best month", numeric, Format("percent"), Formula(:revenue / Col Max(:revenue, year)));
// 2) and 3) The trick is to use Eval Expr() and Name Expr() when defining formulas.
cols = dt2 << get column names;
// add formulas for 2
For(i = 2, i <= 11, i++,
colname = Char(cols[i]) || " by main";
f = Eval Expr(Expr(cols[i]) / :main column);
dt2 << New Column(colname, numeric, formula(Name Expr(f)));
);
// dito for 3
For(i = 2, i <= 6, i = i + 2,
colname = Char(cols[i]) || "+" || Char(cols[i + 1]);
f = Eval Expr(Expr(cols[i]) + Expr(cols[i + 1]));
dt2 << New Column(colname, numeric, formula(Name Expr(f)));
);
```

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

Very helpful for some scripting I was doing for Monte Carlo analysis. Thank you!