topic Re: Writing a formula using "IF" and Quality Control in Discussions

Writing a formula using "IF" and Quality Control

Valley_Gurl — Tue, 24 Jul 2018 15:38:23 GMT

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.

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

Mark_Bailey — Tue, 24 Jul 2018 16:12:27 GMT

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.

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

markschahl — Wed, 25 Jul 2018 23:05:31 GMT

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)

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

Valley_Gurl — Tue, 24 Jul 2018 17:32:04 GMT

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.

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

Mark_Bailey — Tue, 24 Jul 2018 17:43:55 GMT

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

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

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

markschahl — Tue, 24 Jul 2018 17:54:12 GMT

See screenshot.

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

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

Valley_Gurl — Tue, 24 Jul 2018 18:55:35 GMT

Thank you! This will be very helpful.

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

Valley_Gurl — Tue, 24 Jul 2018 19:36:41 GMT

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!

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

gzmorgan0 — Tue, 24 Jul 2018 20:09:19 GMT

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.