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

Question

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

Example Calc_19.jmp

Example Calc.xlsx

txnelson · Accepted Answer

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.

txnelson · Accepted Answer

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.