Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- How can I construct a Simple Time Series Prediction Using Column Formulas?

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Apr 20, 2020 12:16 AM
| Last Modified: Apr 20, 2020 5:06 AM
(2030 views)

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

- Tags:
- t

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Apr 20, 2020 6:20 PM
| Last Modified: Apr 20, 2020 6:31 PM
(1965 views)
| Posted in reply to message from PatrickGiuliano 04-20-2020

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.

Jim

4 REPLIES 4

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How Can I construct a Simple Time Series Prediction Using Column Formulas?

Created:
Apr 20, 2020 5:57 PM
| Last Modified: Apr 21, 2020 1:18 AM
(1968 views)
| Posted in reply to message from txnelson 04-20-2020

@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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Apr 20, 2020 6:20 PM
| Last Modified: Apr 20, 2020 6:31 PM
(1966 views)
| Posted in reply to message from PatrickGiuliano 04-20-2020

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.

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Ho Can I construct a Simple Time Series Prediction Using Column Formulas?

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