Writing a formula using "IF" and Quality Control

[#1]

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]

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.

[#3]

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.

[#4]

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. 

[#5]

See screenshot. 

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

[#6]

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)

[#7]

Thank you! This will be very helpful.

[#8 · Accepted solution]

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

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

[#9]

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!

[#10 · Accepted solution]

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.