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: Writing a formula using "IF" and Quality Control

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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 24, 2018 8:38 AM
(4384 views)

Hello all,

I am very new to JMP. I find that I am able to write formulas quite fine but now want to write one that is a bit more "advanced" in JMP. This is the formula:

A – B (+1 if A >= B)

I should add these are dates (DDMYYY=30Mar2000) and I need the numbers to give me a difference of the total days. I am having a ton of trouble getting the formula to write correctly. Parentheses are not easy to insert or at least not for me. And I am not understanding the "IF" logic comparison.

The next issue is, how do I QC (quality control) or double check the equation/my work?

Your help is greatly appreciated.

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

Going back to your original questions, what if you just use this for the formula:

`A - B + If(A >= B, In Days( 1 ), 0 )`

Learn it once, use it forever!

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

Try:

:A - :B + (:A >=B); //this adds 1 if A is A>=B and zero otherwise

Also if these are dates, the column with the subtraction formula should have the Format as Duration Days. You might also want to investigate the functions floor and ceiling.

If you are working on JMP 12 or later, from the Main Menu select Help > Scripting Index > select Functions for Categories > Select Date Time or search for the function Date Difference() there are 3 examples showing the different options for alignment.

8 REPLIES 8

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

Re: Writing a formula using "IF" and Quality Control

First, I assume that A and B are numeric columns with the date stored as the number of seconds since midnight, January 1, 1904, as required by JMP. You cannot use character string representations of dates for this computation.

Second, see the **Date Difference()** function.

So you want 1 if A >= B? What result do you want if the comparison is false?

You could use just **Date Difference( A, B, "day" ) > 0** if you want 1 when it is true and 0 when it is false.

Learn it once, use it forever!

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

Re: Writing a formula using "IF" and Quality Control

You are correct, A and B are numeric columns. The format is ddMonyyyy, width 10, modeling type continuous.

What I need to happen is have the difference of the two dates (A-B). Using the **Date Difference fuction** as described gives me an error (not sure why). I found that if I do:

(A-B)/86400 I do get the days difference!

To your point of the if than. I need to add one (1) if A is greater than B. I tried to continue the formula by doing ((A-B)/86400)>0 but that gives me only 0 or 1 as a result. When in fact I need it just to add 1 to the number. For example:

A = 02Mar2000

B = 30Mar2000

Then (A-B)/86400 is -28. Now I need my formula to simply add one since A is greater than B.

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

Re: Writing a formula using "IF" and Quality Control

See screenshot.

JSL: If( Date Difference( :A, :B, "day" ), 1, 0)

Highlighted
##

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

Re: Writing a formula using "IF" and Quality Control

Don't be afraid, Formula Editor is your friend! Take it from someone that presented on it a previous Discovery Summit:

Discovery-Summit-2016: Letting-Go-of-Excel-Hello-Formula-Editor

As far as checking your work goes: make a distribution of your new column. Better practice for the plots is to also plot the input variables so that you verify the logic is correct. JMP 14 lets you preview your results at the bottom of the Formula Editor window (nice addition, developers!). In this case, the formula is pretty simple. I analyze a lot of time series process data (thousands of rows of pressure, temperature, flow, etc.) and have to write formulae to find conditions (P > x AND T > y). In those cases, I will also plot the inputs and formula result versus timestamp to see if the formula does what I want.

Here's a Mastering JMP Webinar on the subject by Jerry Cooper (my partner in the video above)

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

Re: Writing a formula using "IF" and Quality Control

Thank you! This will be very helpful.

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

Going back to your original questions, what if you just use this for the formula:

`A - B + If(A >= B, In Days( 1 ), 0 )`

Learn it once, use it forever!

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

Re: Writing a formula using "IF" and Quality Control

That worked (see attached)!

I can see by eye that it works but for a larger dataset, which is what I am working on, this would be harder. I see that others recommended a distribution or y-vs-time, but how would you set up a distribution or y-vs-time to check this work?

Again, this may be very simple but I am so new to JMP and not sure where to begin. Thanks!

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

Try:

:A - :B + (:A >=B); //this adds 1 if A is A>=B and zero otherwise

Also if these are dates, the column with the subtraction formula should have the Format as Duration Days. You might also want to investigate the functions floor and ceiling.

If you are working on JMP 12 or later, from the Main Menu select Help > Scripting Index > select Functions for Categories > Select Date Time or search for the function Date Difference() there are 3 examples showing the different options for alignment.