How can I construct a Simple Time Series Prediction Using Column Formulas?

[#1]

Hello Everyone!  I'm working with a friend of mine who is  a covid-19 data analysis and trending enthusiast and a quality/reliability control engineer  He and I both have an avid interest in basic model prediction for the purposes of putting some simple trend lines based on linear least squares confidence intervals -- I've been fumbling over his sample dataset where he his doing prediction in MS Excel and unfortunately I cannot for the life of me reproduce this in JMP.  Could someone have a look and let me know what I am missing?   I am trying to compute cumulative sums but I don't think I am using the Col Cumulative Sum() function properly, or perhaps I am using the wrong function?  Can use JSL within a column formula to reference specific rows in a data table? 

Basically the crux of my question is: how do I reproduce this relatively simple example in MS Office Excel in JMP?  My Excel sheet and corresponding attempt in JMP are attached. 

Cheers, Patrick

[#2 · Accepted solution]

Attached is a JMP data table that is an exact replication of your Excel calculations.  Now, why you would want to do it this way, given the built in Linear Regression function and/or the many different platforms that will give you the predictions, I don't know, but it was a fun exercise.  To get the exact values, there were a couple of items that had to be dealt with.  The conversion into Excel stored date values from JMP stored date values and also the backwards recursion in the Predicted Infections had to work through, but since the JMP formulas are really a complete programming language it didn't require much to get it done.

[#3]

@txnelson  Thanks so much for this!!  Super helpful, I will study it more and let you know if any questions. 

The proper notation for the column formula's is especially helpful.

You asked why on earth I would ever do it this way?? Good question!  As a learning opportunity: primarily to understand better the mechanics (the calculation) of how prediction works generally, in other words, how to predict future observations on the basis of past observations. 

You mentioned there are a myriad of ways to do this with other platforms in JMP!  I have used Fit Y by X to construct simple linear regression confidence intervals and prediction intervals. I have used graph builder to try and build out predicted values alongside actual values.  

I think I can do the very simple and general example.  Suppose I had Fit a regression line using Fit Model in JMP to do this. Suppose I saved Predicted formula for the fit and I plotted the prediction formula versus the response "Date ID".   Suppose I added some number of future observations to predict on, I might get a graph like this if I plot the observed values, the prediction formula (from an OLS regression) and the upper and lower prediction interval band formulas (at 95% confidence, again for an OLS regression line).  

 

Besides the new "Time Series Forecast"  Platform in JMP15 (which seems to be point and click, although I can't seem to save any sort of prediction formula there),  are there specific ways that data needs to be organized in JMP (especially time-series type data) to facilitate making dynamic predictions? (e.g. by adding N future observations as rows to the data table)?  

thanks again,  @PatrickGiuliano 

[#4 · Accepted solution]

All of the world is not linear.   And as you indicated, a time series to smooth out cyclical terms may also be appropriate. Here are your data fit linearly and with a simple cubic polynomial.

Before pushing into forecasting beyond the limits of the know data, I would suggest more concentration on the determination of what your exact model(s) is/are.  JMP really presents a very nice environment for looking at a large number of prediction models and the comparisons between them.

Concerning the mapping of the Excel work you did, into the JMP paradigm, I approached it as a nice vehicle to help Excel proficient folks as yourself, become more comfortable with the approach that JMP takes.

In most cases, JMP provides the prediction formulas for the models.  It is then just a simple matter of applying that to a data table of future values.

[#5]

thank you again. I agree with your thinking here about looking at better model fitting before thinking about prediction. Cheers.