how to calculate the intersection point x axis y axis values and display the output

Question

Hi all,

I have an ongoint set of data where the x axis is date and y axis is the CRM index. I would like to calculate when the line will hit the lower and upper "green zone". I can do it by formulating through fit y by x and create a prediction formula column and enter the date manually to next empty data row and try and try but I guess there is a better way of doing it. This would also be extremely helpful for calculation the Break-even point during the income statement. Many thanks in advance

scwise · Accepted Answer

Thanks for the question on how to look at modeled data and forecast out to where it will hit a target area.  Gave this some thought and realized that the Prediction Profiler might be able to easily give you the X and Y target coordinates you want!

We created a simple data set with just a few X (Time) and Y (Output) coordinates to demonstrate.  Also went into the Y (Output) column properties and put in some specification limits (Target) so we could replicate your view and try and can see on the graph the extrapolated coordinates that we want to hit.  In this case we put in a target of 50, well above the maximum output value of 40 we had in the data.

Then used the following steps:

- Fitted the line via Fit Y by X menu selection, then select from the red triangle Fit Line.  Then under the Linear Fit red triangle, select Save Predicted to get the formula line saved back to your data table.

- Then select the Graph - Profiler from the top menu and give it the Predicted Output formula column.

- Now you can visually view this by expanding the Time (X) axis (easies way by pulling out directly on the axis) to see where the line will cross the Target.  Switching the Pointer Tool to the Cross Hair Tool will give you a good visual view of the coordinates where the line crosses the target.

- To get the numbers calculated exactly, right click not the Prediction Profiler menu and select Desirability Functions.  Again make sure you have expanded you X axis to include a higher range of time on the Profiler as above.  Then again under the red triangle select Set Desirabilities, and set Match Target with your desired value as the Target.  Note this basically is saying that at 100% Desirability (= 1) you want to be as close to the Target as possible.  Then back on the red triangle say Maximize Desirability.  This should give you your coordinates and in our case we forecast a Time 14.5 to get an Output of 50.

Note that if we instead do this using the Fit Model platform, you can stay in this platform for the entire exercise and will even see calculated confidence intervals (blue dashed lines) around your prediction line by using the Profiler from the red triangle menu.  You can also get fancier with your linear fit in the Fit Model platform (multiple linear regression, etc.) as your prediction of the coordinates at the target are only as good as the fit of your line.  But again we can see that we would still forecast out a Time of 14.5 to hit the Output of 50.

Last consideration might be to utilize a Time Series model if you feel there is any time lags or seasonality to fitting your lines.  Again here you can do some visual prediction directly from the platform, as well as save out the prediction formula for Graph - Profiling - Desirability.

Will attach in the mock up file we used with scripts as well.  Hope this helps!

Scott

Dan_Obermiller · Accepted Answer

There are many good points made in this response that are GREAT skills when working with JMP.

As with most things in JMP, there is usually another way, so here is another approach that you may wish to try. I will use the same data table that was in the previous post.

Fit your model using Fit Model. It can still be a simple straight line model, if you wish.

Then from the Response Output red triangle, choose Estimates > Inverse Prediction.

In the resulting dialog you can specify a confidence level to use because JMP will give you a confidence interval for the time that you will achieve your target.

In the Output window you put in your target value(s) (yes, you can specify more than one target). Clicking OK will give you the estimated time, confidence interval for that time, and a picture. Note that this gives the same result as the previous reply.