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
- :
- Re: How can I calculate by Functions?

- 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

How can I calculate by Functions?

Created:
Feb 26, 2015 6:31 AM
| Last Modified: Oct 18, 2016 3:40 PM
(4447 views)

Dear Community, I need your help to calculate as a new column as MinADR of:

(600+500)-(400+300) / (60+50)-(40+30) placed in the row of Label "b"

7 REPLIES 7

Highlighted
##

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

Re: How can I calculate by Functions?

Created:
Feb 26, 2015 11:43 AM
| Last Modified: Oct 18, 2016 3:41 PM
(4089 views)
| Posted in reply to message from saitcopuroglu 02-26-2015

here's my table

Here's Tables->Summary, Column2->Statistics->Sum, Column1->Group:

Add a formula column to the summary table:

Sum(Column 2) is a really ugly variable name, not a function call.

Getting close:

Tables->Join from the original, join with the summary, use the aabbbccc column to match the rows....

OK

Way back in the summary step you can summarize both of your columns at the same time; I've only shown the numerator columns here. Renaming the columns along the way may help too. The "source" scripts from the intermediate tables might be useful as well if you need to script this for multiple runs.

Craige

Highlighted
##

Uhhhh, so complicated? I'm even unable to replicate your steps Even I could after rerunning the JSL as an automated report production each time the result will be a different named table "unnamed8, unnamed9, unnamed10..." and the script itself will stop rolling and I have no clue how to manage that problem But many thanks for taking your time and sharing your solution !

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

Re: How can I calculate by Functions?

Highlighted
##

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

Re: How can I calculate by Functions?

I'm afraid there may be some misunderstanding about what you're trying to do.

Are you trying to put a computation in an individual cell the way you would with Excel? If so, then that may be the source of your frustration.

JMP's data table isn't a spreadsheet. A formula applies to an entire column not individual cells.

If I've misunderstood, perhaps you could explain a bit more about what you're trying to do and we could provide a simpler solution.

-Jeff

-Jeff

Highlighted
##

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

Re: How can I calculate by Functions?

Dear Jeff,

I solved the problem by creating summary tables adding columns adding formulas to columns and calculating the desired variables. I was wondering if I was able to solve the equation without creating new summary tables. The question is:

Am I able to calculate in a new column the the (subtraction of ColSum of Revenue by one Variable of Label from another) and divide it by the (subtraction of ColSum of Rooms by one Variable of Label from another)..

Highlighted
##

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

Re: How can I calculate by Functions?

Created:
Feb 27, 2015 2:46 PM
| Last Modified: Oct 18, 2016 3:42 PM
(4089 views)
| Posted in reply to message from saitcopuroglu 02-26-2015

Okay, this is significantly less generalized but also less complicated...

Highlighted
##

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

Re: How can I calculate by Functions?

Thank you Kevin,

This would solve the simplified example data table but actually the real data table consist of 600.000 rows of a, b and c

Highlighted
##

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

Re: How can I calculate by Functions?

This column formula seems to work ok for me

If(

:Label == "b" & Lag( :Label, 1 ) != "b",

(Lag( :Revenue, 2 ) + Lag( :Revenue, 1 )) / (Lag( :Room, 2 ) +

Lag( :Room, 1 )),

:Label == "b" & Lag( :Label, 1 ) == "b",

(Lag( :Revenue, 3 ) + Lag( :Revenue, 2 )) / (Lag( :Room, 3 ) +

Lag( :Room, 2 )),

0

)

Article Labels

There are no labels assigned to this post.